November 16, 2015 at 5:54 am
Every time I think I understand something about security, I get my @$$ handed to me again.
What I thought should be a very simple situation: a stored procedure, a schema and a table, all owned by dbo. Stored procedure is in a schema that grants both EXECUTE and SELECT permission to a group login. The procedure reads from a table. It works fine for me as admin, but fails for users with an error message stating that read permission was denied on the table.
If a user, for whatever reason, has EXECUTE permission on a procedure, and the procedure and all objects accessed by the procedure have the same owner, why is further permission needed? When I grant read permission on the table to the same login that already has permission to run the procedure, it all works.
What am I missing here? If a login has permission the run a procedure, isn't it implicit (in a situation like this, with all the same owner) that it has access to whatever the procedure needs to accomplish its task?
November 16, 2015 at 6:10 am
I can see two possible explanations:
1) It is not the way you think it is, but procedure and table have different owners. Check the two objects in sys.objects, and check the column principal_id. If non-null, this indicates a different owner than the schema owner. You also check sys.schemas.
2) The table is access through dynamic SQL. In this case ownership chaining does not apply, as the SQL string is a scope of its own that does not have an owner. It is not part of the procedure.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 16, 2015 at 6:31 am
Erland Sommarskog (11/16/2015)
I can see two possible explanations:1) It is not the way you think it is, but procedure and table have different owners. Check the two objects in sys.objects, and check the column principal_id. If non-null, this indicates a different owner than the schema owner. You also check sys.schemas.
2) The table is access through dynamic SQL. In this case ownership chaining does not apply, as the SQL string is a scope of its own that does not have an owner. It is not part of the procedure.
Yes, my apologies - I didn't mention that, because I didn't realize that it was pertinent. There is a bit of dynamic SQL in the procedure. It performs a pivot, and the columns upon which the pivot is based come from a parameter string, which I dismantle in code and pick out the individual columns required. I have no idea how I would do that without dynamic SQL.
So, given that I'm using dynamic SQL and would really prefer not to give permissions directly on tables, is there a secure solution? Create a view and put it in the same schema, then have the procedure read from the view?
Or is there some way to give the dynamic SQL string one-shot permission within the scope of the procedure?
Or something else altogether?
November 16, 2015 at 6:34 am
You can sign the procedure with a certificate. From the certificate you create a user which you grant the permissions needed.
I describe this technique in detail in an article on my web site:
Granting Permissions through Stored Procedures.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 16, 2015 at 6:54 am
Erland Sommarskog (11/16/2015)
You can sign the procedure with a certificate. From the certificate you create a user which you grant the permissions needed.I describe this technique in detail in an article on my web site:
Great, I'll have a look at that. Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply