July 1, 2009 at 9:30 am
Hi All,
I have a database with a number of tables, all in the dbo schema. Each table has a view that directly reflects each table. these views are also in the dbo schema.
I have an application that needs to interface with the database but I only want the app to have access to a second set of views that will select across a number of the first set of views.
What I have done is created a user, lets call it app. I then created a schema called app_views and made the app user the owner of that schema.
I have then created a view in the app_views schema but when the application tries to select from this view it gets a permission denied error as this view is trying to select from the first set of views in the dbo schema and the app user does not have (nor do I want to give) permissions to the dbo schema.
Am I going about this the wrong way? what's the best way to approach this?
All input welcomed!
Thanks
July 1, 2009 at 1:07 pm
All you need to do is change the owner of the app_views schema to dbo. Then the SQL Server will use ownership chaining to allow access to the base views/tables. The link has an example.
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 2, 2009 at 2:46 am
Thanks for that Jack, it actually cleared a lot of the confusion in my mind around security.
I am unfortunatly still getting the select permission denied error. I have been over it and it seems to logically be the same as your examples.
Any suggestions?
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply