1

I have a process run by a domain account MY_LOGIN that needs to execute a procedure THE_PROCEDURE but only when another specific procedure, called PROCEDURE_TO_TEST is not running.

All this is within one specific database called MY_DATABASE

How can I do for MY_LOGIN to check if the procedure PROCEDURE_TO_TEST is running ?

What permissions should I grant to MY_LOGIN so that it can do this check?

How can I test if this is working?

1 Answer 1

1

I create my PROCEDURE_TO_TEST in a very simple way for testing purposes:

    USE MY_DATABASE
    GO

    CREATE PROCEDURE DBO.PROCEDURE_TO_TEST 
        AS BEGIN

        SELECT 'BEGIN'
        WAITFOR DELAY '00:00:15'
        SELECT 'AFTER'

        END
GO

I execute the stored procedure PROCEDURE_TO_TEST and on a separate window I use the script below to check if it is running. (if it shows up, then it is running)

declare @object int
SELECT @object=OBJECT_ID('DBO.PROCEDURE_TO_TEST')
SELECT @OBJECT

    Select *
                from        (

                                SELECT *
                                FROM sys.dm_exec_requests
                                where sql_handle is not null

                ) r 
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                where t.objectid = @object

I grant the required permissions to MY_USER:

use master
go
GRANT VIEW SERVER STATE TO MY_LOGIN 
GO
use MY_DATABASE
go
GRANT VIEW DEFINITION ON [PROCEDURE_TO_TEST] TO MY_LOGIN 
GO

Because I use OBJECT_ID (Transact-SQL)

A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.

And sys.dm_exec_sql_text (Transact-SQL)

Permissions Requires VIEW SERVER STATE permission on the server.

Now for testing purposes I will run the procedure PROCEDURE_TO_TEST and on a different window I will check if it is running, IMPERSONATING MY_LOGIN.

to do this I use:

EXECUTE AS LOGIN='MY_LOGIN'

AND

--===================================================================================
-- check who is the user running the show, and the original login
-- this is to test the 'execute as login' below
--===================================================================================

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), 
                         CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER 
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

And when I have done the tests, and found out the user can run the script then I come back to be me:

REVERT

now I wrap the script into a procedure that I create called isSPRunning that receives as parameter @ProcedureName and check if @ProcedureName is running:

USE MY_DATABASE
GO

GO
CREATE procedure issprunning 
@ProcedureName VARCHAR(108)
AS
/*
=======================================================================
Script  :   procedure issprunning 

Desc    :   

  Take as parameter a procedure name and returns 1 if it is running
   and 0 if it is not running or the procedure has not been found
   or the user does not have required permissions.


Required Permissions:

   The required permissions to make use of this procedure are:

   1) procedure to run this procedure
   2) VIEW SERVER state
   3) view definition on the @ProcedureName procedure

   the granting of permissions:
   (please replace the username and database name accordingly)


    use master
    go
    GRANT VIEW SERVER STATE TO MY_LOGIN 
    GO
    use MY_DATABASE
    go
    GRANT VIEW DEFINITION ON [PROCEDURE_TO_TEST] TO MY_LOGIN 
    GO
    GRANT EXECUTE ON isSpRunning TO [MY_LOGIN] 
    GO

Usage   :  

         DECLARE @I INT
         EXEC @I = issprunning 'PROCEDURE_TO_TEST'
         SELECT @I


Links   :

=======================================================================
History
Date             Action        User                Desc
-----------------------------------------------------------------------
03-Mar-2017     created       Marcelo Miorelli


=======================================================================
*/
BEGIN 
SET NOCOUNT ON

declare @object int
SELECT @object=OBJECT_ID(@ProcedureName)
--SELECT @OBJECT

RETURN CASE WHEN 
EXISTS(

                Select *
                from        (

                                SELECT *
                                FROM sys.dm_exec_requests
                                where sql_handle is not null

                ) r 
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                where t.objectid = @object

) THEN 1 ELSE 0 END 

END 
GO
GRANT EXECUTE ON dbo.issprunning to [MY_LOGIN] as dbo
GO

In order to make it easier, so that I can pass any procedure name that exists on MY_DATABASE I have extended a the permissions granted, but consider the implications of this in your own environment.

    use MY_DATABASE
    go
    GRANT VIEW DEFINITION TO [MY_LOGIN] 
    GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.