Automatically Force User Disconnects via Scheduled Script

Tags:
Backup and Recovery
Database Management Systems
Security
SQL Server
Greetings all ... What is the most efficient automated script-based approach to terminate user connections for "particular" databases? Where: 1. Know name of database(s) 2. Know name of user(s) 3. Do not know name of user(s) 4. Server must always remain in production and user(s) must not be disconnected from other databases Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Here is a script that can be automated into a scheduled job:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[usp_KillUsers]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[usp_KillUsers]
GO

CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON

DECLARE @strSQL varchar(255)

CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))

INSERT INTO #tmpUsers EXEC SP_WHO

DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ‘Killing ‘ + @spid
SET @strSQL = ‘KILL ‘ + @spid
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bakash
    Here is a script DECLARE @Id INT DECLARE @LastSpid int DECLARE @Msg CHAR(8) DECLARE @Attempts INT SELECT @Attempts = 0 SELECT @LastSpid = 0 WHILE 1 = 1 BEGIN SET ROWCOUNT 1 SELECT @Id = spid FROM master..sysprocesses p, master..sysdatabases d (NOLOCK) WHERE d.name=@DbName AND d.dbid = p.dbid AND spid @@spid AND spid @LastSpid IF (@@rowcount 0) BEGIN SELECT @LastSpid = @Id SELECT @Msg = 'KILL ' + convert(char(8) ,@Id) + ' checkpoint' PRINT @Msg EXEC( @Msg) ELSE BEGIN BREAK END SET ROWCOUNT 0 SELECT @Attempts = @Attempts+1 END EXEC sp_dboption @DbName, 'dbo use only', 'true'
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following