none
EXECUTE AS 'username' permissions error

    Question

  • I am building a sproc that will need the WITH EXECUTE AS 'username', The sql user acct is a memeber of the sysadmin role and I have verified the the user account has the permissions.  I login in on the account and run the procedure I build with the EXECUTE AS CALLER it runs just fine,  I recomple the procedure to EXECUTE AS 'Username'  and I get the following SQL error 'Cannot alter login 'test_4', because it doesn't exist or you do not have permissions'.

    Any Ideas?

    The code looks like this to start:

    CREATE PROCEDURE dbo.usp_reset_facets_password

       @username nvarchar(50)

       @newpass    nvarchar(30)

    WITH EXECUTE AS 'FACETS_Admin'

    AS

    .....

     

    Friday, May 10, 2013 1:15 PM

Answers

  • That is not going to work out. When you use WITH EXECUTE AS you impersonate a database user, not a login. When you impersonate a database user, you are sandboxed into the current database, and you are not able to access other databases or server resources. If you open two doors, you can break out of of the sandbox, but I recommend that you keep one of them closed, as opening both is a security risk.

    Instead what you should do is to sign the procedure with a certificate ahd you have both in the user database and in the master database. You create a login from that certificate (which is not a login that can log in) and grant that the permissions needed for the procedure; judging from the procedure name and the error message that would be ALTER ANY LOGIN.

    For more details on this technique as well as the problems with EXECUTE AS and the security hole I mentioned, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 5:28 PM