January 30, 2009 at 2:46 am
I need some advice! Here is the situation, I have two database - one is the production database and the other holds views that query the tables in the production database. Lets call the production database HRData and the reporting database HRDataReports.
The objects in HRData are owned by a specific schema, lets call this HRScheme, the objects in HRDataReports are all owned by schema dbo.
If I create a new user in HRDataReports and give that user SELECT permissions on the views and then try to execute a SELECT * FROM view statement I get the following error:
The SELECT permission was denied on the object 'view', database 'HRData', schema 'HRSceme'
Is this message due to chaining?? So, the first thing I need to do is correct this problem. The second problem I have is that I want to create indexes on the views in HRDataReports but as I understand I can only do this if the views exist in the same database as the tables they query i.e. the views will have to be in HRData. Is this correct?
Thanks
January 30, 2009 at 3:52 am
In my understanding you do not have a reporting database. A reporting database means you are taking a snapshot of production to separate "production" from "reporting" workload.
If I understood your scenario you have a production database and for some reason -I don't know to hear about- somebody decided to put views on a separate database.
Having said that, the whole of the workload hits Production and as you can see the solution is creating for you more troubles than benefits.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 30, 2009 at 3:59 am
PaulB (1/30/2009)
In my understanding you do not have a reporting database. A reporting database means you are taking a snapshot of production to separate "production" from "reporting" workload.If I understood your scenario you have a production database and for some reason -I don't know to hear about- somebody decided to put views on a separate database.
Having said that, the whole of the workload hits Production and as you can see the solution is creating for you more troubles than benefits.
Yes, you are correct in that the reporting database is not a database that contains snapshot data, the scenario is how you have understood it.
I think the reason for the split is because the database is from a 3rd party but this has not been done for other databases that belong to 3rd party applications.
January 30, 2009 at 5:01 am
Hi,
You are correct. This is an ownership chain issue. You will need to grant select permissions on the objects in the production data.
Also, with regards to indexed views, again you are correct. The tables must exist in the database where the view will be created.
January 31, 2009 at 2:54 am
Clive Strong (1/30/2009)
Hi,You are correct. This is an ownership chain issue. You will need to grant select permissions on the objects in the production data.
Also, with regards to indexed views, again you are correct. The tables must exist in the database where the view will be created.
Thanks Clive. In your opinion, will moving the views to the production database be worthwhile? I am wary of putting the views into a database that does not belong to our company but at the same time I do think that indexing the views will be beneficial especially as I am unable to create indexes on the tables due to us not owning the tables.
January 31, 2009 at 6:28 am
I would get a letter from the vendor telling they are Okay with creating views, then go ahead.
You can word your request in the form "we are planning to create views in XXX database to improve customer satisfaction by helping the database to deliver a more customized service, project is scheduled to be initiated on mm/dd/yyyy. Please let me know if you have anything against that effort on or before the scheduled date".
If they say nothing, you are Okay to go. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply