CREATE Procedure WITH EXECUTE as 'SA'

sorry if repeat question dont know doing wrong here. have created stored proc when pass in parameter of sql login, passes through every database on server , returns perms sql login on every database. request 1 of our managers wants able see users database perms @ point in time running proc. not 'sa' himself, idea create proc set execute sa. whenever ran info needs.

so proc works fine when run 'sa'. need add "execute sa" command wont let me follows

  create procedure dbo.usp_checkuserpermsall   	-- add parameters stored procedure here  	@user varchar(255)    with execute as 'sa'    as    begin  	-- set nocount on added prevent 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 follwing error

msg 15151, level 16, state 1, procedure usp_checkuserpermsall, line 187  cannot execute user 'sa', because not exist or not have permission.

have gone down wrong path here...?

thanks

 

 

sa login, not user. specify in proc definition user, not login. rest, can't comment, since know nothing procedure etc.

---

tibor karaszi, sql server mvp | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

SQL Server  >  SQL Server Security