May 18, 2009 at 6:57 am
Hello!
I have a question regarding views in SQL Server 2005. I have a view that accesses some tables. I want a user to have access to the view but not the underlying table. So I grant select permissions for the user. For the table itself I have not granted nore revoked any permissions att all. But when the user queries the view, error is returned: "The SELECT permission was denied on the object MyTable".
If I grant select permission on MyTable everything works just fine. But that is not what I really want. I want users only be able to access the views. This particular user does not own any schemas and is only member of database role Users. What am I doing wrong here?
May 18, 2009 at 8:06 am
Are the view and the tables in the same schema? You may want to check out this blog post on ownership chaining.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 18, 2009 at 10:37 am
Well, they are not in the same schema, they are even in different databases. I did read the blog post but putting the view and the table in the same schema is not what I want. Are there any other possible solutions?
May 18, 2009 at 12:19 pm
Okay, I did not know that you were crossing databases with the view. By default cross database ownership chaining is disabled so in order for a view to cross to another database you either need to enable cross database ownership chaining or grant appropriate permissions on the the objects in the other database.
The issue with enabling cross database ownership chaining is that it is a server-level setting so you cannot specify the databases you want to allow it on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply