question

jit_coy1109 avatar image
jit_coy1109 asked

how to configure a user that can create view but no access to restricted tables

Platform: SQL 2005 Standard How can we set/configure a user to basically have all permissions to create views, stored procedures and tables, perform insert, update, delete but will not have any access to some specified tables when creating or executing views. any suggestion on handling this kind of scenario? seems like the Grant "Alter" superceed any deny permission on the restricted tables set at the schema level.
securityusers
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
The problem is most likely not that alter is superceding the deny but rather that SQL Server is using ownership chaining. To test this theory, I created 2 schemas. I granted a user "full" access to 1 schema and denied that user any access to the other one. But when that user created a view in his/her schema to look at my secret table, it worked. Why? Because the owner of both schemas (and the underlying objects) was the same, so SQL Server allowed access without checking user permissions. The solution is to make the user the owner of the schema as follows: CREATE USER Q117093 WITHOUT LOGIN; GO CREATE SCHEMA Q117093; GO GRANT CREATE TABLE TO Q117093; GRANT CREATE VIEW TO Q117093; GRANT CREATE PROCEDURE TO Q117093; ALTER AUTHORIZATION ON SCHEMA::Q117093 TO Q117093; Now that user can create objects in that schema (as owner of the schema), but he/she will not be able to see objects in the limited access schema because the ownership chain is broken. The owner of the Q117093 schema (the user with the same name in this case) is not the same as the owner of the other schemas in the database. You will now need to explicitly grant any permissions that user needs outside his/her schema. I hope that answers your question. For more information on ownership chains, please see [this article][1]. [1]: http://technet.microsoft.com/en-us/library/ms188676%28v=sql.105%29.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you. This solution works!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.