There are times when we start the SQL Services in Single User Mode to troubleshoot a critical issue but by the time the services comes up, some Application Logs on to SQL Server. Only way to get rid of this is to ask the Application Team to stop the application and then start the SQL service in Single User Mode.
In SQL Server 2005 and above, a new feature has been provided which allows us to specify the Application which should be allowed to connect to the instance when running in Single User Mode. Here is how I did it on one of the SQL Server 2008 Instances.
Opened up command prompt and issued the below command.
net start mssql$sql2008 /m"SQLCMD"
Note that the /m and the Application Name within double quotes are case-sensitive. In this case the name of the Instance is SQL2008, hence the service name is .ssql$sql2008. The /m switch instructs the Service Control Manager (SCM) to start the SQL Server service in Single User Mode. The Application Name which should be allowed to connect to the SQL Server Instance when running in Single User Mode is entered immediately after /m switch (no space after /m). In this case I chose “SQLCMD”.
Now it does not allow any other application to connect. Would it allow sqlcmd to connect? Yes it did !