Disconnect Users from Database

There are some operations that we execute in SQL Server which need exclusive access to the database. For everyone who has ever attempted this, I’m sure they’ve seen an error like this at least once.

Restore-Failed

There are a couple of ways to handle forcing exclusive access on a database. First we have the ALTER DATABASE method in T-SQL which is likely the most popular and most widely known.

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This method is highly effective. It sets the database to SINGLE_USER mode and the current connection will take ownership of that single user. In addition, the ROLLBACK IMMEDIATE option will KILL all other connections and force their active transactions to rollback.

The ALTER DATABASE method is not bullet-proof, however. For example, exclusive access is required on a database snapshot if you want to restore another database from the snapshot. You cannot set a database snapshot to SINGLE_USER mode which makes gaining exclusive access harder. So let’s look at another T-SQL method for killing all connections to a database, except our own.

declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'MyDB'

set @execSql = ''
select  @execSql = @execSql + 'kill ' +
	CONVERT(char(10), spid) + ' '
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
     AND DBID <> 0
     AND spid <> @@spid
exec(@execSql)

This method is ten lines of code and is iterating through the current processes executing a KILL command via dynamic SQL. I don’t like this method, it feels sloppy to me, but most importantly it is difficult to remember and not quickly typed out. Likely, we all have snippet tools to solve that problem but let me show you another way.

Import-Module SQLPS -DisableNameChecking
$svr = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost";
$svr.KillAllProcesses("Test1");

This PowerShell method is one that I prefer. Its three lines of code which already speeds up the scripting and I find it easier to remember. Also, I tend to add the Import-Module command to my PowerShell profile, thus removing one more line of code.

This article has 2 comments

  1. Heeeey Hammer,
    I am experiencing a layered issue with accessing my databases. Interestingly, it seems the tokens stored for my service account (used to install SQL) are no longer valid. A few mishaps during AD replication caused the service account to be attributed to a useless SID, which also invalidates the expected token used for Windows Authentication into my SQL instances, to include access to ‘master’.
    Originally, I was seeking a solution to place my databases into single user mode, even then, no avail.
    Do you still check this thing?

    • When you say service account, do you mean the account which runs the SQL Server service, therefore the instance will not start? Or, do you only mean database level access? If it is the account running SQL Server, you can just update the account and password in the SQL Server Configuration Manager. If you no longer have access to a valid sysadmin account and need to re-establish access to your databases, check out this link. It shows how to get in as a sysadmin, where you can fix permissions. https://www.sqlhammer.com/help-i-am-locked-out-of-my-sql-server-instance/

Leave a Reply

%d bloggers like this: