Non sysadmin account needs to kill connections

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Non sysadmin account needs to kill connections

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2009-09-17 : 09:34:56

Hi team, I have a good one for you.

I have created a job that has two steps. First it creates a backup of a database, then it

fires a seperate job on a different server that restores the backup file to two databases.

My problem is coming in the second job. The second job has 6 steps total. I won't go into TOO

much detail because the concept is pretty simple--backup on one server and restore on another.

The issue is the account that is running the job. It is a non system administrator account

called rc_user. We want to keep permissions to a minimum of course and there in lies the

problem.

Here is the step causing problems:


--Kills connections
USE MASTER
DECLARE @SQL VARCHAR (25) --variable to hold dynamic TSQL
DECLARE @SPID VARCHAR (25) --variable to hold each spid in the loop

WHILE  (SELECT COUNT ( SPID ) FROM  
		master..sysprocesses p
		JOIN  sys.databases d on p.dbid = d.database_id
		WHERE d.name = 'RIO_ServU_Test') > 0  --boolean expression that will loop until 

its false (until we have no connections)
	BEGIN		--begining of code block to run
		SET @SPID = (SELECT TOP 1 SPID FROM  
				master..sysprocesses p
				JOIN  sys.databases d on p.dbid = d.database_id
				WHERE d.name = 'RIO_ServU_Test')  --set the spid variable to 

grab one of the spid

		SET @SQL =  'KILL '+ CAST (@SPID AS VARCHAR) --set the dynamic sql
		EXEC (@SQL)  --execute the dynamic sql
	END			     --end the loops code block

Our accont of the user, rc_user, does not have the rights to kill connections.

One attempt that was tried but failed was putting this step into a store procedure and then

executing as an admin account, but that did not work either.

Can anyone suggest another way to make this work. If necessary I would be happy to script out

the entire job.

Thanks!

Craig Greenwood

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-17 : 11:00:42

What happened when you used a procedure and EXECUTE AS/ Did you get an error message?

craigwg
Posting Yak Master

154 Posts

Posted - 2009-09-17 : 14:30:01

I don't recall the error. Something about rights I'm sure. But I have some good news. We got this to work this morning in a rather complex way. Here is the explanation. If you'll remember from above, the job fires and backs up the database. Then it fires a second job on another server that does several things: kills connections, restores the database, resets spids, and finally deletes the backup file. Remember too that the account running it was a non sysadmin account.

Step 1 of the second job is to kill connections. If you research you will find non sysadmin accounts can not kill connections. So in step one we fire a batch job called KillConnections.bat which looks like this:

sqlcmd -E -d master -Q "exec KillConnectionsToRIO_ServU_DEV"

The purpose of this batch job is to run a stored procedure, which looks like this:


USE [master]
GO
/****** Object:  StoredProcedure [dbo].[KillConnectionsToRIO_ServU_DEV]    Script Date: 09/17/2009 12:27:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[KillConnectionsToRIO_ServU_DEV]
AS
BEGIN
	SET NOCOUNT ON;

--USE MASTER  --cant use in a SP
DECLARE @SQL VARCHAR (25) --variable to hold dynamic TSQL
DECLARE @SPID VARCHAR (25) --variable to hold each spid in the loop

WHILE  (SELECT COUNT ( SPID ) FROM  
		master..sysprocesses p
		JOIN  sys.databases d on p.dbid = d.database_id
		WHERE d.name = 'RIO_ServU_DEV') > 0  --boolean expression that will loop until its false (until we have no connections)
	BEGIN		--begining of code block to run
		SET @SPID = (SELECT TOP 1 SPID FROM  
				master..sysprocesses p
				JOIN  sys.databases d on p.dbid = d.database_id
				WHERE d.name = 'RIO_ServU_DEV')  --set the spid variable to grab one of the spid

		SET @SQL =  'KILL '+ CAST (@SPID AS VARCHAR) --set the dynamic sql
		EXEC (@SQL)  --execute the dynamic sql
	END			     --end the loops code block

END

This is repeated for each database since I'm restoring the same database twice, but its very clever!

My next problem is that upon completion the client said he needs a group to run the job as opposed to the individual login we currently have running it. I am researching today how to make a group do it. Any one have input on that one?

Craig Greenwood

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-18 : 04:04:16

You need to create a credential and a proxy account. Here is a video tutorial:

http://www.sqlshare.com/media.aspx?vid=147&

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources