Hello.
I faced the following problem: there's a user who has to execute a stored porcedure (spTest). In spTest's body sp_trace_generateevent is called. sp_trace_generateevent requires alter trace permissions and I don't want user to have it. So I would like user to be able to execute spTest. How can I do that?
Thank you for your help.
views:
126answers:
3
A:
When you go to execute that specific stored procedure you will need to create a different connection using the needed user credentials.
Mitchel Sellers
2010-05-12 13:52:33
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
2010-05-12 14:19:51
A:
Try this:
EXECUTE AS user = 'special_user'
EXECUTE YourProcerdure
REVERT
see these:
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)
KM
2010-05-12 13:53:13
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
2010-05-12 14:11:12
from: http://msdn.microsoft.com/en-us/library/ms181362.aspx The user or login name specified in EXECUTE AS <context_specification> 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
2010-05-12 14:21:09
This suggestion doesn't help neither. It appeared that I should turn on TRUSTWORTHY. Thank you anyway.
StuffHappens
2010-05-13 06:38:31
A:
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.
John Sansom
2010-05-12 14:03:05