Using the sysadmin role with EXECUTE AS

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 sa user:

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?

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224))

You are punching a hole in your security by allowing an unprivileged user run as sysadmin role.

If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE

e.g. Below enables lock escalation to the partition level on a partitioned table. If the table is not partitioned, lock escalation is set at the TABLE level.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO); -- valid options are AUTO, TABLE and DISABLE

Now you can just give an unpreviledge user alter table rights.

HTH

StackExchange Database Administrators Q#74679, answer score: 6