2

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.

2
  • 2
    Set it to SINGLE_USER as well and use WITH ROLLBACK IMMEDIATE. Commented Mar 25, 2015 at 14:27
  • @AaronBertrand While googling your answer/comment to see if I missed it somewhere I found this very example READ_ONLY or READ_WRITE requires exclusive access to the database. The following example sets the database to SINGLE_USER mode to obtain exclusive access. . I can't believe I missed that, thank you very much!
    – Reaces
    Commented Mar 25, 2015 at 14:34

1 Answer 1

6

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
1
  • I'm somewhat ashamed to admit I read that blog post about a year ago, and promptly continued to use statement terminators for all of a few weeks... Regardless, thank you for the advice, and the wake-up call to the state of my T-SQL!
    – Reaces
    Commented Mar 25, 2015 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.