Execute a stored procedure as another user

Question

I have the following problem: there's a user, A, who has to execute a stored procedure (spTest). In spTest's body, sp_trace_generateevent is called.

The problem is that sp_trace_generateevent requires alter trace permissions, and I don't want user A to have those permissions. I would still like user A to be able to execute spTest. How can I do that?

score 34 · Accepted Answer · edited Dec 15 '22 at 19:16

34

Try this:

EXECUTE AS user = 'special_user'

EXECUTE YourProcerdure

REVERT

See these links for more information:

Understanding Context Switching <- has examples of things like you are trying to do
Understanding Execution Context
EXECUTE AS Clause (Transact-SQL)
EXECUTE AS (Transact-SQL)

edited Dec 15 '22 at 19:16

TylerH

20,799
66
75
101

answered May 12 '10 at 13:53

KM.

101,727
34
178
212

For some reason it does't help. I get error that i don't have permission to run sp_trace_generateevent. But when I connect to the server as special_user I have no problems to execute sp_trace_generateevent. Any ideas? – StuffHappens May 12 '10 at 14:11
2

from: http://msdn.microsoft.com/en-us/library/ms181362.aspx The user or login name specified in EXECUTE AS must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the EXECUTE AS statement fails. Additionally, IMPERSONATE permissions must be granted on the principal. Unless the caller is the database owner, or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership. – KM. May 12 '10 at 14:21
This suggestion doesn't help neither. It appeared that I should turn on TRUSTWORTHY. Thank you anyway. – StuffHappens May 13 '10 at 06:38

score 3 · Answer 2 · answered May 12 '10 at 14:03

As others have suggested you can achieve what you wish using the Execute As clause. For examples of implementation choices take a look at the Books Online documentation for the Execute As clause.

For further reading and to develop more understanding of this topic, what you are looking to achieve comes under the security concept of Context Switching.

score 0 · Answer 3 · edited Feb 20 '18 at 22:34

0

This is what I did (and succeeded):

let Source = Sql.Database("server", "database", 
    [Query= "EXECUTE AS USER='user' EXECUTE [schema].[spname] 'parm1', 'parm2'"])

in

Source

edited Feb 20 '18 at 22:34

Peter Mortensen

30,738
21
105
131

answered Nov 20 '17 at 12:32

Emilio Pastore

55
5

This doesn't seem to add anything to the existing answers, which already give the solution of `Execute As User= 'user'` – TylerH Dec 15 '22 at 19:17

score -2 · Answer 4 · answered May 12 '10 at 13:52

-2

When you go to execute that specific stored procedure you will need to create a different connection using the needed user credentials.

answered May 12 '10 at 13:52

Mitchel Sellers

62,228
14
110
173

For some reason it does't help. I get error that i don't have permission to run sp_trace_generateevent. But when I connect to the server as special_user I have no problems to execute sp_trace_generateevent. Any ideas? – StuffHappens May 12 '10 at 14:19

Execute a stored procedure as another user

4 Answers4

Linked