How to disconnect all users in sql server except sa?

sql-server sql-server-2008 maintenance
LaBracca picture LaBracca · Oct 25, 2012 · Viewed 13.5k times · Source

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"?

Answer

Andomar picture Andomar · Oct 25, 2012

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More