Sunday, February 26, 2012 12:09 PM
i have created a user in my sql express and granted read access to all tables.When i try to edit table data using this user then server throws error saying permission denied.
I granted execute permission to this user, and tried executing procedure which changes table's data then change is committed to database.
is there any way through which i can prevent user to commit change in my table even through stored procs?Please help
Regards Kumar Gaurav.
Sunday, February 26, 2012 1:26 PM
Permission on the the table (including DENY) will not be checked as long as procedure and table has same owner so that even user is explilctly denied INSERT, UPDATE permission on the table; user can insert, update data through stored procedure assuming user is granted execute permission on the stored proc. If you use dynamic SQL inside stored procedure in that case, user must need permission to insert, update etc. on base table.
The only way you can prevent modification through stored procedure
a) create stored proc in different schema and deny execute permission to that user on that schema or
b) stored proc that perform modifications assign to a different owner than base table (this will break owner ship chain)
- Edited by Chirag Shah Sunday, February 26, 2012 1:27 PM
- Edited by Chirag Shah Sunday, February 26, 2012 1:49 PM
- Edited by Chirag Shah Sunday, February 26, 2012 4:20 PM
- Proposed As Answer by Peja TaoModerator Monday, February 27, 2012 6:01 AM
- Marked As Answer by Peja TaoModerator Friday, March 02, 2012 8:55 AM
Monday, February 27, 2012 4:22 PMThe permission to change the table by using the proc came from "ownership chaining". This is explained here http://msdn.microsoft.com/en-us/library/ms188676.aspx
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty