Schema Permissions - Can't find a Clear Answer to This

Question

So far with my DB I've been slowly granting permission to certain things as I find they are needed for certain roles, but I've hit a problem that I can't see how to get around, and I don't know how to word it so a search engine can find an answer.

At present the roles I am working with have execute permission granted on certain SPs (and little else), I've now built an SP which does an UPDATE on one of the tables. My problem is that I am receiving an error when I run it:

The UPDATE permission was denied on object 'table name', database 'db name', schema 'dbo'.

Having not done anything with schemas so far I looked around the properties and I cannot see how this is supposed to work. I looks to me that I would be granting permission to perform the UPDATE directly on the table without using any SPs.

I don't want to grant permission to users to be able to launch their own statements as that won't be needed in this DB, ever. Infact no one who will use this DB will ever even see any SQL (most wouldn't have a clue anyway), everything is handled behind the scenes.

My understanding was that one of the reasons for SPs was that you could do things in the DB without 'directly' granting permission to users to do it themselves. Rather you simply set the permissions as to which users (or roles) can execute which SPs and then the SPs then do what is needed.

So my question is, how can I get my SPs to run the UPDATE, INSERT, etc. statements without granting those permissions to any users?

Perhaps I'm missing something, I'm not sure...

PS: I've just realised, I already have a SP that does an UPDATE on a different table, but that runs fine. Neither table have any permissions set right now, and the role I'm using has been granted permission to run both SPs, I can't see any difference between the two apart from the fact that the one that works runs the UPDATE directly within the T-SQL code while the other uses 'EXEC xp_executesql' since its running a dynamic statement.

Answer 1

You need to understand Ownership Chains http://msdn.microsoft.com/en-us/library/ms188676(SQL.105).aspx

If the ownership of the table and proc are the same, then access permission is only checked once, at the proc. If different people/schema's own the table and the proc, permission is checked both at the proc and at the table. That second check is probably what's blocking you.

Example:
If user A owns table X and user A owns procedure Y and procedure Y updates table X, then if user A grants execute permission on procedure Y to Joe, then the procedure will work.
If user A owns table X but user B owns procedure Y and procedure Y updates table X, then if user B grants execute permission on procedure Y to Joe, then the procedure will not work because it still needs permission from user A who owns the table.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

In additions to Rick's post, beware that when you create an object in a schema, the schema owner becomes the owner of the object.

From what you say, I see little reason why not all schemas, procedure and tables should be owned by dbo.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

I can't see any difference between the two apart from the fact that the one that works runs the UPDATE directly within the T-SQL code while the other uses 'EXEC xp_executesql' since its running a dynamic statement.

Well, dynamic SQL is a pretty big difference.  Dynamic SQL effectively breaks the ownership chain, requiring permissions on the objects referenced by the procedure.  Using the example from Erland's article (http://www.sommarskog.se/grantperm.html), you could create use a certificate user with the needed permissions and sign the proc with the certificate:

CREATE CERTIFICATE examplecert
    ENCRYPTION BY PASSWORD = 'All you need is love'
    WITH SUBJECT = 'Certificate for example_sp',
    START_DATE = '20020101', EXPIRY_DATE = '21000101';
GO
CREATE USER examplecertuser FROM CERTIFICATE examplecert;
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON testtbl TO examplecertuser;
GO
-- And each time you change the procedure:
ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert
    WITH PASSWORD = 'All you need is love';
GO

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Dan - That certainly makes sense, since dynamic SQL can contain references to anything in the DB. But updating a certificate all the time looks like a lot of long-term hassle to me.

Alternatively I've figured out a method of doing this that (hopefully) gets around the permissions issue. Instead of using dynamic SQL I'm going to use a multi-statement table-valued function to create a table containing the data that will be updated and build a standard SQL statement that uses that.

Thanks for the help.

Answer 5

Dan - That certainly makes sense, since dynamic SQL can contain references to anything in the DB. But updating a certificate all the time looks like a lot of long-term hassle to me.

You don't need to update the certificate each time the proc is altered; just add the signature to the proc again after the alter.  That said, it is certainly easier not to have to remember that step at all.

Alternatively I've figured out a method of doing this that (hopefully) gets around the permissions issue. Instead of using dynamic SQL I'm going to use a multi-statement table-valued function to create a table containing the data that will be updated and build a standard SQL statement that uses that.

I agree a TVF might be a better approach than dynamic SQL, depending on the particulars of your situation.  Be mindful that TVFs don't have statistics so the optimizer might not generate the most optimal plan.  Especially with updates, be sure to check the execution plan to ensure only the data needed by a query is touched.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Dan - That certainly makes sense, since dynamic SQL can contain references to anything in the DB. But updating a certificate all the time looks like a lot of long-term hassle to me.

You don't need to update the certificate each time the proc is altered; just add the signature to the proc again after the alter.  That said, it is certainly easier not to have to remember that step at all.

Alternatively I've figured out a method of doing this that (hopefully) gets around the permissions issue. Instead of using dynamic SQL I'm going to use a multi-statement table-valued function to create a table containing the data that will be updated and build a standard SQL statement that uses that.

I agree a TVF might be a better approach than dynamic SQL, depending on the particulars of your situation.  Be mindful that TVFs don't have statistics so the optimizer might not generate the most optimal plan.  Especially with updates, be sure to check the execution plan to ensure only the data needed by a query is touched.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Thanks for the extra info, I'll bare that in mind.

Answer 7

Dan - That certainly makes sense, since dynamic SQL can contain references to anything in the DB. But updating a certificate all the time looks like a lot of long-term hassle to me.

Yes and no. If you check out my article, it includes a script how you can create a certificate and sign the procedure with a throw-away password. If the procedure is changed, the old cert is thrown out and new is created.

Then again, dynamic SQL is often over-used and it seems that you have found an alternate solution for your particular case.

---

Erland Sommarskog, SQL Server MVP, [e-mail]