Wednesday, July 31, 2013

Changing the Restrict Access Option in SQL Server

Ever get something like this annoying popup when you try to change a SQL Server database from SINGLE_USER to MULTI_USER?


This is what always happens to me when a database is already locked down into SINGLE_USER mode and I try to change the property in the GUI. In order to change it, I run this T-SQL in SSMS (SQL Server Management Studio):

ALTER DATABASE [MyDatabase]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;

If there is already an operation going on in the database, the "WITH ROLLBACK IMMEDIATE" will take things back to their previous state before the transaction started. After the statement completes, more than one authorized user should be able to access the database. Always use caution when performing any ALTER DATABASE statement on a production server. When a database is locked down in SINGLE_USER mode, an icon of a person will appear next to the database in SSMS:


If you refresh your view of the databases in object explorer after running the above T-SQL, the person icon should disappear from the database indication that it is in MULTI_USER mode.