It is my understanding that I can use the
EXECUTE AS OWNER clause as part of a procedure that I create to make the body of that procedure run as a different user. My goal is to execute a command that requires the
sysadmin role (
DBCC TRACEON(1224)). This procedure is supposed to be called by an unprivileged user.
I ran the following script under the
SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin') -- dbo 1 1 IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'MyProc') DROP PROCEDURE MyProc GO CREATE PROCEDURE MyProc WITH EXECUTE AS OWNER AS SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin'); -- dbo 1 0 DBCC TRACEON(1224) --Msg 2571, Level 14, State 3, Procedure MyProc, Line 7 --User 'dbo' does not have permission to run DBCC TRACEON. RETURN GO EXEC MyProc
Output is inline in comments. It turns out that outside of the procedure I seem to have
sysadmin membership, but not inside the procedure.
The procedure is owned by the
dbo user. I understand that it is not possible to grant the
sysadmin role to a database user (at least the GUI doesn't offer this possibility). So I don't see how I could ever make a database user have a server role.
I also tried
EXECUTE AS 'sa' which results in
Cannot execute as the user 'sa', because it does not exist or you do not have permission.. The documentation states that I can only specify a user name, not a login name. So I understand why that didn't work.
How can I run my procedure with
sysadmin role membership?