Using the sysadmin role with EXECUTE AS

Question

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?

Answer 1

It can be done but it's generally considered fairly dangerous. At a very basic level you set the trustworthy flag on the database and then when use you execute as on the sp it can take advantage of it's server level principals security access.

Because of how dangerous this is I don't want to go into any detail here. However I've blogged about it here with specific instructions on how to do it.

All that being said make very sure you absolutely NEED to do it this way. You are opening a big security hole. If you do decide to do it put your SP in it's own database and only grant users connect and execute access to the sp.

Answer 2

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

Answer 3

Creating a SQL Server Agent job (owned by a sysadmin member) will do the trick, although I realize that this is not a very pretty solution.

The user can start the job (ansynchronously) using msdb.dbo.sp_start_job. Running an Agent job synchronously, however, requires a few more lines of code if this is a requirement. Also, obviously, you need to have the SQL Server Agent service up and running.