locked
Only one dev should be able to run a certain proc RRS feed

  • Question

  • I need to prevent "dbo"s on our development team from accidentally running a certain stored proc. Yet, I want a specific developer to be able to execute the proc. I have tried, unsuccessfully, to accomplish this using SQl permisisons. Any suggestions appreciated.

     


    TIA,

    barkingdog

     

    Monday, August 4, 2008 10:03 PM

Answers

  •   As Rich suggested, you could use role memberships to have a clean distinction between users who should be able to call your module and the ones that shouldn't. For example:

     

    CREATE ROLE AuthorizedModuleCallers

    CREATE USER [schema_demo] WITHOUT LOGIN

    go

    CREATE SCHEMA [schema_demo] AUTHORIZATION [schema_demo]

    go

    CREATE PROC [schema_demo].[sp_Demo]

    AS

        IF(is_member('AuthorizedModuleCallers')=1)

        BEGIN

            print 'Authorized to execute'

        END

        ELSE

        BEGIN

                declare @ErrorMessage nvarchar(100)

                SET @ErrorMessage = N'unauthorized caller.'

                RAISERROR (@ErrorMessage, -- Message text.

               10, -- Severity

               1  -- State

               ); -- First argument supplies the string.

        END

    go

     

    -- Executing as dbo

    EXEC [schema_demo].[sp_Demo]

    -- RESULT: unauthorized caller.

    go

     

    CREATE user SPcaller WITHOUT LOGIN

    EXEC sp_addrolemember 'AuthorizedModuleCallers', 'SPcaller'

    -- Grant permission to execute the SP to the role

    --

    GRANT EXECUTE ON [schema_demo].[sp_Demo] TO [AuthorizedModuleCallers]

    go

     

    -- Run as SPcaller for testing:

    EXEC( 'EXEC [schema_demo].[sp_Demo]' ) AS USER = 'SPcaller'

    -- Result: Authorized to execute

    go

     

      I agree with your assessment that that you can only stop dbo from executing by accident, without going through details, dbo owns the DB and can easily work around any restriction.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

    Wednesday, August 6, 2008 8:39 PM

All replies

  •  

    In the past, I have done this by checking the user in the stored proc. Using "SELECT system_user, current_user", you can see that I am a dbo but I can alter the sp code to limit my powers.

     

    Code Snippet

    SELECT  system_user, current_user

    -- ad\67620 & dbo

     

    CREATE PROCEDURE myProc AS
    BEGIN
     IF system_user = 'AD\67620'
     BEGIN
      PRINT 'ALLOWED'
      -- ALLOWED CODE
     END
     ELSE
     BEGIN
      PRINT 'NOT ALLOWED'
      -- NOT ALLOWED CODE
     END
    END

     

     

    Tuesday, August 5, 2008 11:47 AM
  • BTW, You may need to encrypt (WITH ENCRYPTION) the stored proc to avoid tampering. You will need to keep a local copy for yourself to avoid losing the content.

     

    Tuesday, August 5, 2008 11:49 AM
  • An interesitng concept but I don't want to hardcode any names in the stored proc. I'm hoping to create a database role containing a single user and only allowing members of that role to run the stored proc.

     

    I am not trying to prevent evil doers from tampering with the code (though that wouldn't be bad!)  only to prevent accidental execution of the stoerd by by other dbas.

     

    TIA,

     

    barkingdog

     

    Tuesday, August 5, 2008 3:24 PM
  • You could use a IS_MEMBER ('yourole') = 1 check rather than hardcoding names?

     

    HTH!

    Tuesday, August 5, 2008 4:03 PM
  •   As Rich suggested, you could use role memberships to have a clean distinction between users who should be able to call your module and the ones that shouldn't. For example:

     

    CREATE ROLE AuthorizedModuleCallers

    CREATE USER [schema_demo] WITHOUT LOGIN

    go

    CREATE SCHEMA [schema_demo] AUTHORIZATION [schema_demo]

    go

    CREATE PROC [schema_demo].[sp_Demo]

    AS

        IF(is_member('AuthorizedModuleCallers')=1)

        BEGIN

            print 'Authorized to execute'

        END

        ELSE

        BEGIN

                declare @ErrorMessage nvarchar(100)

                SET @ErrorMessage = N'unauthorized caller.'

                RAISERROR (@ErrorMessage, -- Message text.

               10, -- Severity

               1  -- State

               ); -- First argument supplies the string.

        END

    go

     

    -- Executing as dbo

    EXEC [schema_demo].[sp_Demo]

    -- RESULT: unauthorized caller.

    go

     

    CREATE user SPcaller WITHOUT LOGIN

    EXEC sp_addrolemember 'AuthorizedModuleCallers', 'SPcaller'

    -- Grant permission to execute the SP to the role

    --

    GRANT EXECUTE ON [schema_demo].[sp_Demo] TO [AuthorizedModuleCallers]

    go

     

    -- Run as SPcaller for testing:

    EXEC( 'EXEC [schema_demo].[sp_Demo]' ) AS USER = 'SPcaller'

    -- Result: Authorized to execute

    go

     

      I agree with your assessment that that you can only stop dbo from executing by accident, without going through details, dbo owns the DB and can easily work around any restriction.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

    Wednesday, August 6, 2008 8:39 PM