locked
Minimum permissions required to execute proc that references objects in a different schema RRS feed

  • Question

  • So here's what I'm trying to do.

    I have a database role.  (Role_1), which should only have EXEC permissions on a schema (Schema_1).  I will add users to this role.

    I have a stored procedure Schema_1.Proc_1 which takes some parameters and inputs them into a table.  (Schema_1.Table_1)  The only twist is that I have a foreign key on Table_1 which refers to Schema_2.Table_2.  And then I also have a UDF as a default constraint which points to Schema_3.UDF_1.

    My problem is that when I grant EXEC permissions to Schema_1 to the database role and try to execute Proc_1, I get an error saying that I don't have permissions on Schema_2.Table_2 or Schema_3.UDF_1.

    I've currently bypassed that issue with AS EXECUTE AS OWNER (since I'm dbo), but I'm pretty sure that's not the best way to solve this problem.  I assume it has to do with permission chaining between schemas, etc, etc...but the lightbulb is not currently going on. 

    Any ideas?


    • Edited by WellOfDavid Thursday, September 1, 2016 9:30 PM
    Thursday, September 1, 2016 9:29 PM

Answers

  • My problem is that when I grant EXEC permissions to Schema_1 to the database role and try to execute Proc_1, I get an error saying that I don't have permissions on Schema_2.Table_2 or Schema_3.UDF_1.

    It seems you may have dynamic SQL in the proc, which will break the ownership chain.  A better solution than EXECUTE AS in that case is with module signing.  See http://www.sommarskog.se/grantperm.html#Certificates for a more through discussion and examples.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Teige Gao Friday, September 2, 2016 1:33 AM
    • Marked as answer by WellOfDavid Friday, September 2, 2016 5:52 AM
    Friday, September 2, 2016 12:25 AM

All replies

  • >assume it has to do with permission chaining between schemas

    Yes.  Ownership chaining will prevent permission checks on referenced object only if the stored procedure's schema is owned by the same user as the other object's schema.

    If only allow admins to create objects in this database, then you can simply have dbo own all the schemas.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 1, 2016 9:35 PM
  • So, I am an admin (sysadmin actually), and I did create all the objects, and all of the schemas are owned by dbo.

    SELECT * FROM sys.schemas

    shows principal_id = 1 for all the schemas involved.

    When I execute Schema_1.Proc_1 under my account, no problems, but if I connect as a user that only exists in Role_1, I get the permission errors.

    Thursday, September 1, 2016 9:52 PM
  • Can you post a small repro?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 1, 2016 9:55 PM
  • Will do.  Let me put up a sanitized simple script.
    Thursday, September 1, 2016 10:49 PM
  • I think the issue is with the UDF being called by the constraint.

    Can you run a Profiler trace to capture the permissions check


    Martin Cairney SQL Server MVP

    Friday, September 2, 2016 12:07 AM
  • My problem is that when I grant EXEC permissions to Schema_1 to the database role and try to execute Proc_1, I get an error saying that I don't have permissions on Schema_2.Table_2 or Schema_3.UDF_1.

    It seems you may have dynamic SQL in the proc, which will break the ownership chain.  A better solution than EXECUTE AS in that case is with module signing.  See http://www.sommarskog.se/grantperm.html#Certificates for a more through discussion and examples.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Teige Gao Friday, September 2, 2016 1:33 AM
    • Marked as answer by WellOfDavid Friday, September 2, 2016 5:52 AM
    Friday, September 2, 2016 12:25 AM
  • Thank you Dan!!  I always forget about how dynamic SQL breaks the ownership chain.  You're correct, I am calling sp_executesql to perform the insert.  Let me sign the proc and validate that it solves the issue.
    Friday, September 2, 2016 12:38 AM
  • Yup, that was it. Thanks for the answer and the link. I've read it a thousand times, but it's an article that keeps on giving.
    Friday, September 2, 2016 5:52 AM
  • Yup, that was it. Thanks for the answer and the link. I've read it a thousand times, but it's an article that keeps on giving.

    Thanks David!

    Friday, September 2, 2016 7:10 AM
  • And thanks Erland!

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, September 2, 2016 12:00 PM