Menu
How can I test if the SQL Server database is in Single User Mode in a SQL script?
569
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Right-click the database to change, and then select Properties. In the Database Properties dialog box, select the Options page. From the Restrict Access option, select Single.
Example 1: We can use the DATABASEPROPERTYEX() function to check the database user access mode. Example 2: We can also use the sys. databases catalog view to check the database user access mode. Example 3: Below examples shows how we can check whether database is in a MULTI_USER access mode.
To do this, open "SQL Server Configuration Manager", choose "SQL Server Services", then choose the corresponding SQL Server instance, right-click on it and choose "Startup Parameters". As a startup parameter, we specify "-m" that means that the service will start in single-user mode.
To change the database mode using SSMS, open SQL Server Management Studio Connect to the database engine Expand Databases Right-click on AdventureWorks2017. In the database properties dialog box, click on Options. Click on Restrict Access drop-down box and select SINGLE_USER. Click OK to save the configuration.
You can check the sys.databases view ...
if (SELECT user_access_desc FROM sys.databases WHERE name = 'YourDb') = 'SINGLE_USER' begin print 'It is in single user mode!' end If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
