Setting Single-User Mode

Single-user mode can be set for the entire SQL Server or for individual databases using the SQL Server Management Studio. Once this mode has been set, only one connection to the SQL Server or specified database is allowed. Some administrative tasks can only be performed if single-user mode has been set.

Procedure

Single-User Mode for the SQL Server

1. Before you set single-user mode for the server make sure no users are connected to the server.
2. Open the SQL Server Configuration Manager.
3. Choose SQL Server Services.
4. Right-click the SQL Server service (MSSQLSERVER), and choose Properties.

   The SQL Server (MSSQLSERVER) Properties dialog box opens.

5. On the Advanced tab, open the Startup Parameters listbox
6. Add the parameter -m.
7. Press Ok or Apply to confirm the entry.
8. Restart the SQL Server service.

   Only one connection to the server is now possible.

   Caution

   When single user mode has been set, only one connection to the server is permitted. Therefore, keep the following in mind:

   If the SQL Server Agent is running, it occupies the only connection available to the server and you will be unable to establish a connection with the SQL Server Management Studio. Stop the SQL Server agent to enable a connection with the SQL Server Management Studio.

Single-User Mode for a Database

1. Before you set single-user mode for the database, make sure no connection to the database is open.
2. In the SQL Server Management Studio, expand the server. Expand Databases.
3. Right-click the <SAPSID> database and choose Properties.

   The Database Properties dialog box appears.

4. On the Options tab, change the option Restrict Access to Single user. Confirm your entry with OK.
   Note

   Once single user-mode has been set, only one connection to the database is permitted.