question

Sagar Bhargava avatar image
Sagar Bhargava 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.
stored-proceduressecuritypermissions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DazyParker avatar image
DazyParker answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.