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

 

 

Answer 1

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!

Answer 2

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

?

Answer 3

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

Answer 4

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

:-)