Menu
I faced the following problem: there's a user who has to execute a stored procedure (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?
568
To grant permissions on a stored procedureExpand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search.
If you've READ permission on database, you can read data only from Tables, Views, and Functions. But to execute stored procedures, you need to provide permission explicitly to user.
Btw, db_owner is a database ROLE in SQL Server , not a permission. Or if you want the user to execute all current and future stored procedures and scalar-valued functions: grant execute on schema::dbo to User for a single schema, or just grant execute to User for the whole database.
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)
103
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.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
