Who is connected when in Single User Mode

  • Article

Here is how to find out who is connected to your server when you are in single user mode:

METHOD 1:

-------------

à Start your SQL Server in normal multi-user mode and enable login auditing for both successful and failed logins

à Open Management Studio

à Right click on server name àProperties àSecurity tab

à You can check the radio button for both failed and successful logins

à Now go ahead and start your SQL Server in single user mode:

à Browse to the location of sqlservr.exe and run the following cmd:

Sqlservr.exe –m –c

-c: Indicates that an instance of SQL Server is started independently of the Microsoft Windows NT Service Control Manager. This option is used when starting SQL Server from a command prompt, to shorten the amount of time it takes for SQL Server to start.

-m: Indicates to start an instance of SQL Server in single-user mode. Only a single user can connect when SQL Server is started in single-user mode.

à Now you if any connection is made to your SQL Server will be logged in in SQL Server error log and will also be displayed in the command prompt where SQL Server is started.

à Location of SQL Server error logs will be: ”<SQL install drive>\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG”

METHOD 2:

-------------

à Start your SQL Server in normal multi-user mode

à Run the following query

à Select * from sys.sysprocesses

( You will be able to see the loginame, Host name, Domain name, hostprocesses, program_name which will tell you about all the connections made to your SQL Server when you start it. select session_id from sys.dm_exec_sessions WHERE is_user_process = 1 returns the list of user processes)

à So before starting your SQL Server in single user mode if you go ahead and stop all other applications and users from connecting to SQL Server you will should be able to use your third party application to make a connection and restore you master database.

METHOD 3:

----------------

à While starting your SQL server in single user mode you can use a trace flag 4010 which will allow only shared memory protocol to connect to SQL server

à You would be able to connect only within local box where SQL Server is running using tools like Management Studio

à Browse to the location of sqlservr.exe and run the following cmd:

Sqlservr.exe –m –c –T4010