asked
Stored procedure permissions
Hi All, I have a scenario where a user can execute stored procedures but not able to modify structure or data. I am aware that If the user has the permission to execute the stored procedure - that's all he needs to execute that procedure - no matter what that procedure does. If a login has Db_datareader / Public / Execute / View definition permissions on a database and no other permissions explicitly granted, will he be able to update the data or modify the table manually or through stored procedure? Is there a way we can prevent data changes through stored procedures? Thanks.
Under Database->Security, you can configure your rights of SP (Stored Procedure) i.e., in the managements studio's Object explorer, [Database] -> Security -> [YourRole] -> Rightclick for properties -> Securables section
You can, as @DazyParker says, go through the SSMS user interface to grant permissions; however, you might find it easier / more productive / more repeatable / scriptable / maintainable etc to learn the appropriate bits of T-SQL: GRANT EXECUTE ON < > TO < > I would strongly recommend reading up on the [GRANT][1] / [DENY][2] / [REVOKE][3] commands, and then have a think about grouping users so that you're not assigning privileges one at a time. [1]:
https://msdn.microsoft.com/en-us/library/ms188371.aspx [2]:
https://msdn.microsoft.com/en-us/library/ms173724.aspx [3]:
https://msdn.microsoft.com/en-us/library/ms187719.aspx
19 People are following this question.
