locked
Using sp_grantlogin inside a stored procedure that uses EXECUTE AS. RRS feed

  • Question

  • Hello to all you oh mighty of the mightiest programming gods!

    I am a novice in need of help.

    I have a stored procedure

    ALTER

     

    PROCEDURE [dbo].[vm_upd_user_rights]

    @Username

    nvarchar(40)

    WITH

     

    ENCRYPTION, EXECUTE AS 'ABCD'

    AS

    BEGIN

     

    EXEC sp_grantlogin @Username

     

    EXEC sp_change_users_login 'auto_fix', @Username

    END

    Great now i'll have to carry on in blue... anyway so the 'ABCD' user is a member of the sysadmin server role.

    But when I go to execute the stored procedure I get the following error:

    Msg 15247, Level 16, State 1, Procedure sp_grantlogin, Line 13

    User does not have permission to perform this action.

    Msg 15247, Level 16, State 1, Procedure sp_change_users_login, Line 170

    User does not have permission to perform this action.

    I'm using SQL Server 2008. What am I doing wrong?

    Hair pulling out stuff this. Please help.

    Many Thanks

    Mark

    Friday, November 19, 2010 4:30 PM

Answers

  •    One thing to consider for this scenario is the scope DB vs. server.

      The two operations that you are trying to execute in this case are server scoped:

    ·         CREATE LOGIN (internally called by sp_grantlogin) DDL is server-scoped, not database scoped. Granting ALTER ANY LOGIN permission as Willy recommended would be necessary for this operation.

    ·         According to BOL, in order to call sp_change_users_login with the auto_fix option, you need sysadmin role membership (http://msdn.microsoft.com/en-us/library/ms174378.aspx).

      Even following the least-privilege principle, it seems like you will need to give “ABCD” login full sysadmin privileges for this particular scenario.

      Now, for the reason why the module is failing at this point: EXECUTE AS in a module is (by default) a DB-scoped impersonation. The impersonated token would be sandboxed to the current database, without any server (login) privileges.

      In order to extend the privileges for the impersonated context to be server-scoped, you would need to explicitly set a trust relationship (i.e. grant AUTHENTICATE SERVER to the authenticator) either via digital signatures or via the trustworthy bit. For example:

    CREATE LOGIN ABCD WITH PASSWORD = 'S0m3 p@ssword!'

    go

    -- This is not an interactive login, used only for impersonation

    --

    ALTER LOGIN ABCD DISABLE

    go

     

    EXEC sp_addsrvrolemember 'ABCD', 'sysadmin';

    go

     

    CREATE USER ABCD

    go

     

    CREATE PROCEDURE [dbo].[vm_upd_user_rights] @Username nvarchar(40)

    WITH

    ENCRYPTION, EXECUTE AS 'ABCD'

    AS

    BEGIN

        -- Optional: Allow execution only in master DB

        --

          if( db_id() = 1 )

          BEGIN

                EXEC sp_grantlogin @Username;

                EXEC sp_change_users_login 'auto_fix', @Username;

          END

          ELSE

          BEGIN

                PRINT 'This SP can only be executed in master DB'

          END

    END

     

    -- Create signing certificate

    --

    CREATE CERTIFICATE [cert_vm_upd_user_rights]

          ENCRYPTION BY PASSWORD = 'P@ssw0rd for signing cert!'

          WITH SUBJECT = 'Signing cert for vm_upd_user_rights'

    go

     

    ADD SIGNATURE TO [dbo].[vm_upd_user_rights] BY CERTIFICATE [cert_vm_upd_user_rights]

          WITH PASSWORD = 'P@ssw0rd for signing cert!'

    go

     

    -- Optional: destroy the signing private key

    --

    ALTER CERTIFICATE [cert_vm_upd_user_rights]  REMOVE PRIVATE KEY

    go

     

    -- Grant AUTHENTICATE SERVER to the login mapped to this certificate

    --

    CREATE LOGIN [cert_vm_upd_user_rights] FROM CERTIFICATE [cert_vm_upd_user_rights]

    go

    GRANT AUTHENTICATE SERVER TO [cert_vm_upd_user_rights]

    go

     

     For more details, I would like to recommend http://msdn.microsoft.com/en-us/library/ms188304.aspx

      I hope this information helps,

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, November 20, 2010 3:27 AM