locked
MS SQL stored procedure access (inherit) RRS feed

  • Question

  • Hey Guys, 

    I wonder is there way to provide execute permission on MS SQL 2005 STORED PROCEDURE for a user, but don't provide the user any permission on TABLE? i.e. I need to restrict user to make any modification on TABLE directly, he should be able do this only through STORED PROCEDURE.

    Friday, April 13, 2012 11:54 AM

Answers

  • Artak,

    This is exactly one of reasons to use stored procedures.  The basic principle is called "ownership chains" and is described here for SQL Server 2005:

    http://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx

    Basically, however, if your stored procedure and the tables it uses are in the same database you can:

    GRANT EXECUTE ON TheStoredProc TO Joe
    REVOKE SELECT ON SecretTable FROM Joe

    In this case, Joe has no rights to select from SecretTable.  However, the owner of TheStoredProc has rights to select from SecretTable.  Therefore, when Joe is granted execute rights on TheStoredProc, the rights of the stored procedure owner are used to access SecretTable.

    The key is (1) grant rights to the stored procedures and (2) revoke rights from the tables and/or views. 

    RLF

    • Proposed as answer by Iric Wen Monday, April 16, 2012 8:50 AM
    • Marked as answer by Artak Gabrielyan Monday, April 16, 2012 11:22 AM
    Friday, April 13, 2012 1:05 PM