CODEWITHSUNDEEP
sql-serversql-server-2005permissions
StuffHappens
StuffHappens

Reputation: 6557

Execute a stored procedure as another user

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?

Upvotes: 23

Views: 103990

Answers (4)

KM.
KM.

Reputation: 103717

Try this:

EXECUTE AS user = 'special_user'

EXECUTE YourProcerdure

REVERT

See these links for more information:

Upvotes: 35

Emilio Pastore
Emilio Pastore

Reputation: 55

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

Upvotes: 0

John Sansom
John Sansom

Reputation: 41899

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.

Upvotes: 3

Mitchel Sellers
Mitchel Sellers

Reputation: 63136

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

Upvotes: -2

Related Questions