Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
Your article describes a way to have the procedure in a fresh database. That would work. Is it still "dangerous" doing that? It seems that getting the configuration right with a separate database is not that involved. –  usr Aug 22 at 15:23
    
Putting the sp in a new DB is certainly the least dangerous. The danger here is really that at some point down the line someone will mess up and grant someone db_owner of that database. That would effectively give them sysadmin if they know how to do it. I think (haven't tested) you could even do it with CREATE PROCEDURE and impersonate permissions. It's not so much the risk right now but the risk after everyone has forgotten why you created a separate db in the first place. –  Kenneth Fisher Aug 22 at 15:52

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

share|improve this answer
    
This is a nice trick for this specific trace flag, but not for trace flags in general. Also, if you want to prevent the user from performing other alter table activities, you could have a DDL trigger that captures ALTER_TABLE and checks exactly what they're doing before allowing it to happen (well, more accurately, before not rolling it back). –  Aaron Bertrand Aug 22 at 16:27
    
This is a viable solution for me given the fact that it seems to be risky to implement security right. I'll probably use this. Thanks! That said, I'll accept the answer that best answers the security part of this question. –  usr Aug 22 at 16:30
    
@AaronBertrand agreed that this is only for this trace flag 1224 as this trace flag operates at table level. I wrote this answer as looking at the trace flag definition, it can be alternatively enabled using Alter Table. –  Kin Aug 22 at 16:56

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.

share|improve this answer
    
I require the trace flag to be set in the current session on a specific connection. I don't want to enable it globally. –  usr Aug 22 at 15:11
    
Sorry, didn't realize it was session-specific. –  Daniel Hutmacher Aug 22 at 15:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.