Tag: SQL Server Single User Mode

SQL Server Single User Mode enablement

The Single user mode could be enabled for the user defined database. Lets see what could be the available options for you as a DBA Administrator for enabling single user mode.

While enabling this feature ensure that multiple users are not CONNECTED as the time of change, else all user connections gets closed without warning.

You should have DATABASE ALTER Permission to perform this activity.

Ensure that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF . If enabled , you will NOT be able to access the Database in Single User mode.

SQL Server Single User Mode using SQL Server Management Studio

Please find below the steps for enabling Single User Mode using SQL Server Management Studio .

  1. Connect to Microsoft SQL Server Instance
  2. Expand the Object Explorer and select the Database oracleappshelpDB
  3. Right click on Database oracleappshelpDB and select Properties
  4. Select Properties -> Options
  5. Select Restrict Access ->Single
  6. In case of multiple users connected to the Database Open Connection message will be shown. Click “Yes” to close all connections.

SQL Server Single User Mode using Transact SQL

Please find below the steps for enabling Single User Mode using SQL 

USE master;
GO
ALTER DATABASE oracleappshelpDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE oracleappshelpDB
SET READ_ONLY;
GO