locked
Granting execute permission to all procedure with select query RRS feed

  • Question

  • 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 12:09 PM

Answers

  • 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 4:20 PM
    • Proposed as answer by Peja Tao Monday, February 27, 2012 6:01 AM
    • Marked as answer by Peja Tao Friday, March 2, 2012 8:55 AM
    Sunday, February 26, 2012 1:26 PM
  • The 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

    • Proposed as answer by Papy Normand Monday, February 27, 2012 9:28 PM
    • Marked as answer by Peja Tao Friday, March 2, 2012 8:55 AM
    Monday, February 27, 2012 4:22 PM

All replies

  • 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 4:20 PM
    • Proposed as answer by Peja Tao Monday, February 27, 2012 6:01 AM
    • Marked as answer by Peja Tao Friday, March 2, 2012 8:55 AM
    Sunday, February 26, 2012 1:26 PM
  • The 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

    • Proposed as answer by Papy Normand Monday, February 27, 2012 9:28 PM
    • Marked as answer by Peja Tao Friday, March 2, 2012 8:55 AM
    Monday, February 27, 2012 4:22 PM