sql-serverdatabasesql-server-2008maintenance-mode

How to disconnect all users in sql server except sa?

I would like to disconnect all users from SQL Server except sa.

The need for this is:

I wrote a db maintenance utility for my ERP. Before running it I need to ask all users to logoff. So somehow I would like to give them a message (through ERP) "disconnecting in 5 minutes, please save your work and logoff or you'll be kicked out" and then after 5 minutes run the command on the server that disconnects all people. I want "sa" or anyway "1 specific user" not to be disconnected, since the db maintenance utilty will use that user for db connection.

I found this:

use master
alter database MyDatabase set offline with rollback immediate

but how to say "one specific user is an exception"?

Solution

  • Use single_user instead of offline:

    ALTER database [DatabaseName] SET single_user WITH rollback immediate;

    The initial "single user" will be the one issuing the alter database command. You could then proceed to only allow specific users to log on:

    ALTER login [LoginName] disable;

    Also, if you want to rollback the "single user" setting, just return it to MULTI_USER

    ALTER database [DatabaseName] SET MULTI_USER;