locked
Access to KILL command RRS feed

  • Question

  • I'm writing as a developer, not as a DBA, hoping that someone can help me give some advice to our DBA team.

    I would like access to the KILL command for my own commands.  I know that access to the KILL command is an all-or-nothing grant but would it be possible to write a wrapper procedure or function that would allow the user to only kill their own SPIDs?  The DBAs understandably don't want to give access to just anyone to kill any SPID they want, but this is a shared reporting box that is not really a production box, but we still want to lock down who can kill what.

    So could the DBAs grant me EXEC on a wrapper procedure that executes a KILL after checking to make sure a particular SPID is mine without granting me KILL explicitly?

    Thanks!

    Monday, March 16, 2015 7:46 PM

Answers

  • You can sign a procedure with a certificate and grant the permissions to a login created from the cert.

    Something like this.

    use master
    
    go
    create procedure KillMySpids(@SpidToKill int = null)
    as
    begin
    
      declare @spid int;
    
      declare c cursor local for
    	  select session_id
    	  from sys.dm_exec_sessions  s
    	  where s.login_name = ORIGINAL_LOGIN()
    	  and s.session_id <> @@spid
    	  and (s.session_id = @SpidToKill or @SpidToKill is null);
    
      open c;
      fetch next from c into @spid;
      while @@FETCH_STATUS = 0
      begin
        declare @sql varchar(200);
    	set @sql = 'kill ' + cast (@spid as varchar(20));
    
    	print @sql;
    	exec (@sql);
        fetch next from c into @spid;
      end
    
      close c
      deallocate c
    
    
    
    end
    
    go
    
    CREATE CERTIFICATE SpidKillerCert
        ENCRYPTION BY PASSWORD = N'My Spid Killer Cert Password !@#$'
        WITH SUBJECT = N'Certificate for allowing users to kill their own spids.';
    GO
    
    create login SpidKiller from certificate SpidKillerCert;
    
    GO
    
    grant alter any connection to SpidKiller
    
    GO
    
    add signature to KillMySpids by certificate SpidKillerCert
        with password =N'My Spid Killer Cert Password !@#$'
    
    go
    grant execute on KillMySpids to public
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, March 16, 2015 8:07 PM

All replies

  • A quick clarification... I'm not just running queries through SSMS.  I have some instances where I'm creating SQL Agent jobs programmatically so once they're started there's no "window" where I just click stop.  I've seen this in a few other posts.  I just haven't found anything helpful in other articles or forums.

    Thanks.

    Monday, March 16, 2015 7:51 PM
  • not tested..but try this..

    also, kill will have to rollback the transactions so, it might take same duration to kill the process, sometimes, not wise thing to do...

    Create Procedure usp_Kill(@sessionid int)
    with execute as caller
    as
    if (select 1 from sys.dm_exec_sessions where login_name=suser_name() and session_id=@sessionid)=1
    begin
    declare @sql nvarchar(100)
    set @SQL='kill '+cast(@Sessionid as varchar(20))
    execute sp_executesql @SQL
    End
    else
    begin
    select 'cannot kill.not your own session buddy'
    end


    Hope it Helps!!

    Monday, March 16, 2015 8:01 PM
  • A quick clarification... I'm not just running queries through SSMS.  I have some instances where I'm creating SQL Agent jobs programmatically so once they're started there's no "window" where I just click stop.  I've seen this in a few other posts.  I just haven't found anything helpful in other articles or forums.

    Thanks.

    do you have permissions to do that. you can stop a job using  sp_stop_job. syntax can be found here: 

    https://technet.microsoft.com/en-us/library/aa260308%28v=sql.80%29.aspx?f=255&MSPPError=-2147217396

    --this query can get list of all the currently running jobs

    Select @@SERVERNAME as [DBServer],C.Name as [JobName], run_requested_date,start_execution_date,stop_execution_date
    from (Select max(Session_id) as Session_Id from msdb.dbo.syssessions) A
    INNER JOIN msdb.dbo.sysjobactivity B on A.Session_id=B.Session_ID
    INNER JOIN msdb.dbo.sysjobs C on B.job_id=C.Job_ID
    where B.stop_execution_date is null AND B.run_requested_date is not null


    Hope it Helps!!


    • Edited by Stan210 Monday, March 16, 2015 8:05 PM
    Monday, March 16, 2015 8:04 PM
  • You can sign a procedure with a certificate and grant the permissions to a login created from the cert.

    Something like this.

    use master
    
    go
    create procedure KillMySpids(@SpidToKill int = null)
    as
    begin
    
      declare @spid int;
    
      declare c cursor local for
    	  select session_id
    	  from sys.dm_exec_sessions  s
    	  where s.login_name = ORIGINAL_LOGIN()
    	  and s.session_id <> @@spid
    	  and (s.session_id = @SpidToKill or @SpidToKill is null);
    
      open c;
      fetch next from c into @spid;
      while @@FETCH_STATUS = 0
      begin
        declare @sql varchar(200);
    	set @sql = 'kill ' + cast (@spid as varchar(20));
    
    	print @sql;
    	exec (@sql);
        fetch next from c into @spid;
      end
    
      close c
      deallocate c
    
    
    
    end
    
    go
    
    CREATE CERTIFICATE SpidKillerCert
        ENCRYPTION BY PASSWORD = N'My Spid Killer Cert Password !@#$'
        WITH SUBJECT = N'Certificate for allowing users to kill their own spids.';
    GO
    
    create login SpidKiller from certificate SpidKillerCert;
    
    GO
    
    grant alter any connection to SpidKiller
    
    GO
    
    add signature to KillMySpids by certificate SpidKillerCert
        with password =N'My Spid Killer Cert Password !@#$'
    
    go
    grant execute on KillMySpids to public
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, March 16, 2015 8:07 PM
  • @Stan210 - So you think if the DBA grants me access to execute usp_Kill then I should be in good shape?  Why won't that require me to have rights to KILL?
    • Edited by Matt S- Monday, March 16, 2015 8:31 PM
    Monday, March 16, 2015 8:30 PM
  • For more details on the technique that David suggests, you can refer your DBA to this article on my web site, where I discuss this in detail:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 16, 2015 10:43 PM