CREATE Procedure WITH EXECUTE as 'SA'

Question

CREATE Procedure WITH EXECUTE as 'SA'

schwizla

SSCrazy

Points: 2668
More actions
September 21, 2010 at 9:46 am

#224197

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 following 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I believe, that because you are accessing other databases, you need to sign the procedure with a certificate. I don't think EXECUTE AS will work. See this article which says:

However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error. To understand the reason for this default behavior, consider the following scenario.

You may also be able to do this by granting impersonation on sa to the proper user. See this article.

I'd actually recommend against impersonating SA. I'd do the signing and use that to grant only the privileges needed.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

schwizla SSCrazy Points: 2668 More actions · Answer 2

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

Thanks for looking 🙂

sturner SSC-Insane Points: 22411 More actions · Answer 3

make sure you add 'sa' as a user in the database in question. You could also try EXECUTE AS OWNER

The probability of survival is inversely proportional to the angle of arrival.

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 4

A couple of things:

First, make sure the sa account is enabled on the Status page of the SA accounts Properties dialog box.

Second, move the EXECUTE AS sa into the body of the stored procedure and end the stored procedure with REVERT command to make sure that all of the permissions are set back to the caller's level.

For what you are doing REVERT may not be necessary, but it is a good habit to get into.

When you create more sophisticated stored procedures you only want to use EXECUTE AS and REVERT around the code that actually requires the higher permission levels. That way no one gets carte blanche for the entire process.