SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Kick a User from Database


How to Kick a User from Database

Author
Message
shield69
shield69
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
SET NOCOUNT ON

DECLARE @LoginName SYSNAME
DECLARE @DatabaseName SYSNAME
DECLARE @NumberOfTimesToLoop INT
-- Time to wait between checks
-- should be between 1 to 599 seconds.
DECLARE @TimeToCheckKillInSec INT

SET @LoginName = 'sa'
SET @DatabaseName = 'master'
SET @NumberOfTimesToLoop = 2
SET @TimeToCheckKillInSec = 3


DECLARE @WaitTimeStr VARCHAR(8)
DECLARE @i INT
DECLARE @sCurrentSPIDToKill VARCHAR(32)
DECLARE @sSPID VARCHAR(32)
DECLARE @ServerVersion NVARCHAR(128)

SET @sSPID = CAST(@@SPID AS VARCHAR(32))

IF OBJECT_ID('tempdb..#utbSPWHO2', 'TABLE') IS NOT NULL
DROP TABLE #utbSPWHO2

CREATE TABLE #utbSPWHO2 (
SPID INT,
[Status] NVARCHAR(128),
LoginName NVARCHAR(128),
HostName NVARCHAR(128),
BlockedBy NVARCHAR(32),
DBName NVARCHAR(128),
Command NVARCHAR(128),
CPUTime NVARCHAR(64),
DiskIO NVARCHAR(64),
LastBatchRunTime NVARCHAR(64),
ProgramName NVARCHAR(512),
SPID2 INT,
RequestID INT)

-- The clustered index uses the
-- current @@SPID to make
-- sure that the index name
-- is unique.
EXEC('CREATE CLUSTERED INDEX
CI_#utbSPWHO2_' + @sSPID + '
ON #utbSPWHO2 (LoginName, DBName, SPID)')

-- sp_who2 is version-sensitive - RequestID is returned
-- for SQL 2K5 and later but not in SQL 2K.
SET @ServerVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))

-- Get the sp_who2 results.
IF @ServerVersion LIKE '8.%' OR @ServerVersion LIKE '7.%'
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2)
EXEC sp_who2
ELSE
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2, RequestID)
EXEC sp_who2


IF NOT EXISTS(
SELECT *
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName)
BEGIN
PRINT('The user is not currently using the DB')
RETURN
END

-- @WaitTimeStr is used in
-- a WAITFOR DELAY command later.
SET @WaitTimeStr = '00:0' + CAST(@TimeToCheckKillInSec/60 AS CHAR(1))
+ CASE WHEN (@TimeToCheckKillInSec%60) < 10
THEN ':0'
ELSE ':'
END
+ CAST(@TimeToCheckKillInSec%60 AS VARCHAR(2))

-- Get the first SPID to kill.
-- HostName is checked as well
-- to make sure we don't kill
-- internal machine processes.
SELECT @sCurrentSPIDToKill = CAST(MIN(SPID) AS VARCHAR(32))
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName
AND RTRIM(LTRIM(HostName)) <> N'.'
AND SPID <> @@SPID

WHILE @sCurrentSPIDToKill IS NOT NULL
BEGIN
-- Launch the KILL command.
EXEC('KILL ' + @sCurrentSPIDToKill)

-- Check if the process was killed
TRUNCATE TABLE #utbSPWHO2

IF @ServerVersion LIKE '8.%' OR @ServerVersion LIKE '7.%'
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2)
EXEC sp_who2
ELSE
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2, RequestID)
EXEC sp_who2


SET @i = 1

WHILE EXISTS(
SELECT *
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName
AND SPID = CAST(@sCurrentSPIDToKill AS INT))

AND @i <= @NumberOfTimesToLoop

BEGIN
-- Wait and check if the
-- process was killed again.
WAITFOR DELAY @WaitTimeStr

-- Check if the process was killed
TRUNCATE TABLE #utbSPWHO2

IF @ServerVersion LIKE '8.%' OR @ServerVersion LIKE '7.%'
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2)
EXEC sp_who2
ELSE
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2, RequestID)
EXEC sp_who2


SET @i = @i + 1
END

-- Check if loop exited
-- because process was killed
-- or due to too many loop.
IF @i > @NumberOfTimesToLoop
BEGIN
-- Too many loop.
-- Abort and notify the user.
RAISERROR('The script could not kill process id = %s.', 16, 1, @sCurrentSPIDToKill)
RETURN
END
ELSE
PRINT('Process id ' + @sCurrentSPIDToKill + ' was killed successfully')

-- If we got this far then
-- the process was killed
-- and we need to find the
-- next process to kill.
SET @sCurrentSPIDToKill = NULL

SELECT @sCurrentSPIDToKill = CAST(MIN(SPID) AS VARCHAR(32))
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName
AND RTRIM(LTRIM(HostName)) <> N'.'
AND SPID <> @@SPID
END
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search