[SQL]DB user remove (kick out)

 

DB user remove (kick out)👎

Here is the Query.  Change your dbname to use. 

USE master; -- get out of dbname myself

GO

-- kick all other users out:

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE [dbname] SET OFFLINE; -- option 1 prevent sessions from re-establishing connection:

--After finishing DB kick out mode

ALTER DATABASE [dbname] SET ONLINE; -- when you used option 1 use this to return online from offline

ALTER DATABASE [dbname] SET MULTI_USER; --return multi user from single user

Here is another method to kill the user's process ID.

   ALTER LOGIN report DISABLE

--Kill existing sessions

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'

FROM master..sysprocesses 

WHERE dbid = db_id('dbname') AND spid <> @@spid --dbname need to update

EXEC(@kill);

ALTER LOGIN report ENABLE

You can use both methods to prevent this. See the below Query. 

Declare @Database varchar(50) = 'dbname' --dbname need to update

    ALTER LOGIN report DISABLE

--Kill existing sessions

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'

FROM master..sysprocesses 

WHERE dbid = db_id(@Database) AND spid <> @@spid --dbname need to update

EXEC(@kill);

        

        --set DB for SINGLE_USER

IF (SELECT user_access_desc FROM sys.databases WHERE name = @Database) = 'MULTI_USER'

ALTER DATABASE @Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE

        --after finishing all kick out process

IF (SELECT user_access_desc FROM sys.databases WHERE name = @Database) = 'SINGLE_USER' ALTER DATABASE @Database SET multi_user ALTER LOGIN report ENABLE