July 16, 2009 at 12:51 pm
I am kinda new to managing permissions in 2005, hence this question.
I have a user that is connecting via ODBC. I want to give the user access to a view, but not to the underlying tables.
I have given the user access to the view and when I connect with their id, it tells me that I need permission to the underlying tables.
I can get the view to work by giving permission to the underlying tables, but then the user can access the tables if they know the names.
(it's in the documentation for the project so they'll know them)
Thanks in advance for any responses to this post.
July 16, 2009 at 1:03 pm
Is the view in the same schema as the table? If not, then the schemas need to have the same owner. I actually have a blog post about this which you can find here.
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
July 16, 2009 at 1:33 pm
Jack Corbett (7/16/2009)
Is the view in the same schema as the table? If not, then the schemas need to have the same owner. I actually have a blog post about this which you can find here.
Both view and underlying tables have the same owner.
(I can't bring up your blog post in IE. I had to bring it up in Firefox. Just in case you cared.)
July 16, 2009 at 2:05 pm
Huh, that is very odd. Both the need to grant permissions and the fact the blog won't display in FF (that's what I use).
Are the objects (views, tables) in the same database?
What if you use SSMS and do an EXECUTE AS that user and select from the view? This will show if it is SQL Server or ODBC.
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
July 22, 2009 at 7:12 am
(I can't bring up your blog post in IE. I had to bring it up in Firefox. Just in case you cared.)
Firefox works, IE doesn't.
July 22, 2009 at 7:14 am
Jack Corbett (7/16/2009)
Huh, that is very odd. Both the need to grant permissions and the fact the blog won't display in FF (that's what I use).Are the objects (views, tables) in the same database?
What if you use SSMS and do an EXECUTE AS that user and select from the view? This will show if it is SQL Server or ODBC.
The view points to table in the database and outside of the database. I only want to give access to the view, not the underlying tables directly. If they know the name of the tables and the other databases, then they will have access when I give them public access to the other databases.
July 22, 2009 at 7:29 am
Your permissions issues is due to the fact that the view is accessing a table outside the database. In 2000, cross-database ownership chaining was enabled by default, in 2005 that is disabled by default as part of the effort to reduce surface area. You can enable cross database ownership chaining although it is not recommended. Here is the BOL entry for it, http://msdn.microsoft.com/en-us/library/ms188694(SQL.90).aspx
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply