Sql-server – Implications of setting READ_Write

lockingread-only-databasesql serversql-server-2012t-sql

I am looking to update several users on READ_ONLY databases.
As the databases are used 24/7 globally, and maintenance windows have just past, I would like to do this during a low-activity moment (to be able to adhere to deadlines).

I'm currently considering using:

USE MASTER
GO
ALTER DATABASE SQL SET READ_WRITE
GO
USE SQL
GO
EXEC sp_addrolemember N'db_datareader', N'USER'
GO
USE MASTER
GO
ALTER DATABASE SQL SET READ_ONLY
GO

However I want to:

  1. Guarantee this doesn't hang indefinitely
  2. Be sure nothing can be written to the database during this action

As I can't put these commands in a transaction, and I don't have an application server handy on the DEV or STG environments to test the changes beforehand, is there any documentation about changing read_only states for single actions? (I did test the T-SQL statements, and they work)

Also, as this requires an exclusive lock on the database, am I even likely to get my statement through?

I'm inclined to just put forward that this will have to wait until the next maintenance window, period. But I'm hoping someone here can prove me wrong.

Best Answer

You can set the database to SINGLE_USER so that no other session can come in while you are performing your changes. Also, sp_addrolemember has been deprecated; you should be using ALTER ROLE instead. Finally, please get in the habit of using statement terminators.

USE master;
GO

ALTER DATABASE SQL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SQL SET READ_WRITE;
GO

USE SQL;
GO

ALTER ROLE db_datareader ADD MEMBER [USER];
GO

USE master;
GO

ALTER DATABASE SQL SET READ_ONLY;
ALTER DATABASE SQL SET MULTI_USER;
GO
Related Question