locked
permissions to update table directly and through SP RRS feed

  • Question

  • Hi,

    Please correct me if I am wrong. If user has execute permissions on some SP and that SP contain statements INSERT, UPDATE, DELETE, then user will be able to successfully execute the SP with all those statements even if he does not have correct INSERT, UPDATE, DELETE permissions on that particular table?

    Thanks

    Sunday, January 17, 2016 4:14 PM

Answers

  • It depends.

    If the table has the same owner as the procedure, the user only needs EXECUTE permission on the procedure thanks to ownership chaining. If the owners are different, the user needs direct permissions on the table as well. (Because ownership chaining does not apply.)

    Also, if the procedure operates on a table in a different database, the user typically needs permission on the tables.

    Sunday, January 17, 2016 5:51 PM
  • Hi jori5,

    We need to verify that if the SP "Home.ChangeHomeAdress" and the "Users.Information" table are in the same database.

    If the SP “Home.ChangeHomeAdress" and the "Users.Information" table are in the same database, based on my test, the SP "Home.ChangeHomeAdress" can be executed successfully.  There is a blog for your reference.

    However, if the SP “Home.ChangeHomeAdress" and the "Users.Information" table are not in the same database, you should turn on the cross-database ownership chaining using the following code. At  the same time, the two database should owned by a same login that have access permission to both databases, then the ownership chain will be present and the SP can be executed successfully.

    EXECUTE sp_configure 'show advanced', 1;
    RECONFIGURE;
    EXECUTE sp_configure 'cross db ownership chaining', 1;
    RECONFIGURE;


    For more information of using “cross db ownership chaining”, please refer to these links.
    https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx
    https://bolt-tc.com/Technologie/Microsoft-Business-Intelligence/Post/1411/Cross-Database-Ownership-Chaining

    Regards,
    Ice fan


    Ice Fan
    TechNet Community Support


    Tuesday, January 19, 2016 8:37 AM

All replies

  • It depends.

    If the table has the same owner as the procedure, the user only needs EXECUTE permission on the procedure thanks to ownership chaining. If the owners are different, the user needs direct permissions on the table as well. (Because ownership chaining does not apply.)

    Also, if the procedure operates on a table in a different database, the user typically needs permission on the tables.

    Sunday, January 17, 2016 5:51 PM
  • Correct statement from Erland, in addition to know more  'ownership chaining' on this refer below link

    https://technet.microsoft.com/en-us/library/aa905173(v=sql.80).aspx


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, January 18, 2016 6:27 AM
  • One additional question, so if we have "Home.ChangeHomeAdress" SP that inserts into "Users.Information" and both schema's "Home" and "Users" has same owner let's say dbo, SP "Home.ChangeHomeAdress" will be executed successfully?

    Thanks

    Monday, January 18, 2016 7:39 AM
  • Good Day,

    If the user has execute permission on SP and doesn't have READ/WRITE permission on the selected tables then the execute command will fail for that user.

    Thanks,

    Sajith.

     


    http://sqllive.wordpress.com/

    Monday, January 18, 2016 11:42 AM
  • really?

    I thought if both SP and table schema owners are the same (dbo in this case) SP will run successfully as ownership chaining will be activated in this way?

    Thanks

    Monday, January 18, 2016 12:43 PM
  • If the user has execute permission on SP and doesn't have READ/WRITE permission on the selected tables then the execute command will fail for that user.

    This is incorrect. The EXECUTE command will always succeed. The operation on the table will fail, if the table is owned by a different principal than the procedure owner.

    Monday, January 18, 2016 10:43 PM
  • One additional question, so if we have "Home.ChangeHomeAdress" SP that inserts into "Users.Information" and both schema's "Home" and "Users" has same owner let's say dbo, SP "Home.ChangeHomeAdress" will be executed successfully?

    Probably. However, it is possible for users to own procedures or tables in someone else's schema.

    Monday, January 18, 2016 10:44 PM
  • Hi jori5,

    We need to verify that if the SP "Home.ChangeHomeAdress" and the "Users.Information" table are in the same database.

    If the SP “Home.ChangeHomeAdress" and the "Users.Information" table are in the same database, based on my test, the SP "Home.ChangeHomeAdress" can be executed successfully.  There is a blog for your reference.

    However, if the SP “Home.ChangeHomeAdress" and the "Users.Information" table are not in the same database, you should turn on the cross-database ownership chaining using the following code. At  the same time, the two database should owned by a same login that have access permission to both databases, then the ownership chain will be present and the SP can be executed successfully.

    EXECUTE sp_configure 'show advanced', 1;
    RECONFIGURE;
    EXECUTE sp_configure 'cross db ownership chaining', 1;
    RECONFIGURE;


    For more information of using “cross db ownership chaining”, please refer to these links.
    https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx
    https://bolt-tc.com/Technologie/Microsoft-Business-Intelligence/Post/1411/Cross-Database-Ownership-Chaining

    Regards,
    Ice fan


    Ice Fan
    TechNet Community Support


    Tuesday, January 19, 2016 8:37 AM