I am looking to update several users on
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:
- Guarantee this doesn't hang indefinitely
- 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.
SINGLE_USERas well and use
WITH ROLLBACK IMMEDIATE.
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!