Menu
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"?
408
Right-click on a database in SSMS and choose delete. In the dialog, check the checkbox for "Close existing connections." Click the Script button at the top of the dialog.
You need to dynamically build a script that will do that.SELECT N'ALTER LOGIN ' + QUOTENAME(sp.name) + N' DISABLE;' FROM sys. server_principals sp WHERE sp. type IN ('S','U','G','E','X') AND sp.name LIKE LIKE N'%AD\'; 'S','U','G','E','X' represent different login types, see the docs.
You can use the CREATE PROCEDURE or CREATE FUNCTION statement to write and compile a user-defined routine, which controls and monitors the users who can read, modify, or create database tables. You can use the CREATE VIEW statement to prepare a restricted or modified view of the data.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
