none
CREATE Procedure WITH EXECUTE as 'SA'

    Question

  • Sorry if this is a repeat question but I dont know what I am doing wrong here. I have created a Stored Proc which when you pass in a parameter of SQL Login, it then passes through every database on the server and returns all the perms for that SQL Login on every database. This is a request from one of our Managers you wants to be able to see a users database perms at any point in time by just running the proc. As he is not 'sa' himself, my idea was to create a proc the set it to EXECUTE as SA. So that whenever he ran it he would get the info he needs.

    So my proc works fine when I run it as I am 'sa'. I now need to add the "Execute as SA" command but it wont let me as follows

    CREATE PROCEDURE dbo.usp_CheckUserPermsAll 
    	-- Add the parameters for the stored procedure here
    	@user varchar(255)
    
    WITH EXECUTE AS 'sa'
    
    AS
    
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;
    	
    DECLARE @DBsTable TABLE(DBaseID INT NOT NULL, DBaseName VARCHAR(255))
    DECLARE @wdbid INT, @DBName VARCHAR(255), @SQL2 nVARCHAR(max), @SQL nVARCHAR(max)
    ......
    ......
    

    I get the follwing error

    Msg 15151, Level 16, State 1, Procedure usp_CheckUserPermsAll, Line 187
    Cannot execute as the user 'sa', because it does not exist or you do not have permission.

    Have I gone down the wrong path here...?

    Thanks

     

     

    Tuesday, September 21, 2010 3:19 PM

Answers

  • sa is a login, not a user. What you specify in the proc definition is a user, not a login. As for the rest, we can't comment, since we know nothing about what the procedure does etc.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, September 22, 2010 5:22 AM

All replies

  • First, that's pretty dangerous. I cannot recommend using sa to run a stored proc.

    That being said, if you absolutely must do it you can ensure the database owner is sa and then change your statement to run with "execute as owner" or "Execute as 'dbo'". It will run as sa.

    Cheers!

    Tuesday, September 21, 2010 3:49 PM
  • Hi,

    All databases are owned by 'sa' already. I modified the proc to run as both Execute as owner and execute a 'dbo'. Then when I run the proc with a test user (which is DBO in the database in which the proc resides, zero results are returned. Also now when I run as myself (sa) I dont get results either

    ?

    Tuesday, September 21, 2010 3:54 PM
  • sa is a login, not a user. What you specify in the proc definition is a user, not a login. As for the rest, we can't comment, since we know nothing about what the procedure does etc.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, September 22, 2010 5:22 AM
  • thanks for the replies, I assumed you could execute as Login 'sa' as I saw other references online doing just that.

    I didint bother to include the code as I specifically just wanted to over come that problem of EXECUTE as 'sa'

    Anyway all I was doing was reading from sys.database_pricipals and other permission related tables from each database so all I needed to go was grant read permission on every database to the user. I then removed the execute as clause and its working fine

    :-)

    Friday, September 24, 2010 10:54 AM
  • I know this is an old and quasi-answered thread, but since it's one of the first results to pop on Google when searching for this issue, I figured I'd throw in my 2 cents.

    As Tibor answered, 'SA' is a (instance-level) login; not a (database-level) user.  Users are listed under Security -> Users in the specific DB you're creating the SP in.  Notice how 'sa' does not appear on any database within the instance?  This is the reason why you were getting the explicit error.

    To emulate elevated execution privileges for a SP, you have multiple options, but the following two are probably the two most common ways used. 

    The first is to qualify the SP with the DBO schema and specify EXECUTE AS OWNER (as the owner is the schema here).  e.g. CREATE PROCEDURE [dbo].[blahblah]... WITH EXECUTE AS OWNER ...

    The other option is to create the procedure using WITH EXECUTE AS 'DBO', as this is a valid user within the db (as dbo DOES appear under the users folder within the database).

    In either situation, the SP will run with elevated rights of the db_owner role in the database since dbo is a part of the db_owner database role by default.  I just had a developer ask me about this error and without actually reading it first, I did a quick google search and came across this thread.  Had to think for a moment and then had a Eureka moment which I hope will prove helpful to anyone else that stumbles across this thread in the future.

    NOTE: This is just for the context of escalated security permissions within a database only, not on the instance as the original question implies.

    John


    John Eisbrener - http://dbaeyes.com/


    • Edited by John Eisbrener Wednesday, October 30, 2013 7:20 PM Clarification Required on My Reply
    Wednesday, October 30, 2013 4:30 PM
  • ...

    As Tibor answered, 'SA' is a (instance-level) login; not a (database-level) user.  Users are listed under Security -> Users in the specific DB you're creating the SP in.  Notice how 'sa' does not appear on any database within the instance?  This is the reason why you were getting the explicit error.

    To emulate elevated execution privileges for a SP, you have multiple options, but the following two are probably the two most common ways used. 

    The first is to qualify the SP with the DBO schema and specify EXECUTE AS OWNER (as the owner is the schema here).  e.g. CREATE PROCEDURE [dbo].[blahblah]... WITH EXECUTE AS OWNER ...

    The other option is to create the procedure using WITH EXECUTE AS 'DBO', as this is a valid user within the db (as dbo DOES appear under the users folder within the database).

    In either situation, the SP will run with elevated rights of the db_owner role in the database since dbo is a part of the db_owner database role by default.

    ..

    Sorry to correct:
    To run with elevated privileges there are basically two options:

    1. using trustworthy (not recommended)
    2. using certificate signed procedures

    Actually here is a more recent thread on the same subject.. for anyone else stumbling accross this: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work?forum=sqlsecurity

    Please be aware that dbo is not a member of db_owner but rather special user withing each database which cannot be denied (huge difference vs db_owner)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Wednesday, October 30, 2013 4:46 PM
  • I agree, sysadmin is NOT the equivalent as db_owner.  My post is specific to the context of elevated permissions only within the context of a specific database, not elevated permissions within the instance.  I was trying to provide clarification on escalated security context within an SP, which admittedly was not the original intent of the question.

    Good catch and I do appreciate the opportunity for clarification.  I've also updated my original post to reflect this.

    John


    John Eisbrener - http://dbaeyes.com/

    Wednesday, October 30, 2013 7:20 PM
  • For a longer and a deeper discussion on EXECUTE AS, certificates and why you
    should not use TRUSTWORTHY, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 31, 2013 10:28 PM