SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ownership chaining


ownership chaining

Author
Message
laker_42
laker_42
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2077 Visits: 275

We are setting up our new CRM environment. It involves several databases. We have an all database that contains union views of the other CRM databases. The goal is to give everyone read access to the views & not to the underlying base tables. I am having some trouble getting this to work. Here is what it looks like:

SQL 2005 environment
DB1 - CRM DB #1
DB2 - CRM DB #2
DBAll - union views DB

Test user - has datareader access to DBAll

I have database chaining enabled for all 3 database. I have DBAll set to trustworthy. All objects are owned by dbo.
When I do a select from one of the views, I get a message saying:

"The server principal "testuser" is not able to access the database "DB#1" under the current security context.

I have read through BOL & did some searches online but can't figure out where my issue is.

Any ideas?

THanks,
John





laker_42
laker_42
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2077 Visits: 275

Ok, I figured out my issue. The test user still has to be assigned to the public role in DB1 & DB2. It doesn't have to have any further privileges. Way too of a simple fix that I was overlooking.

Now to add a twist to this. I have a stored procedure in DBAll that is inserting into tables in DB2. This works fine with DB chaining if the stored procedure does not build a dynamic SQL statement. However, if it is building a dynamic SQL statement, I get insert permission denied on the table in DB2. Is there a way to get around this with dynamic SQL? Do I need to use the EXECUTE AS for any dynamic SQL sps?

I was able to figure out the issue with dynamic SQL. Thanks to this very good article: http://www.sommarskog.se/grantperm.html. When using dynamic SQL you do need to use the WITH EXECUTE AS statement or use certificates. As the article states, this is an option you will want to be careful with & work closely with your local friendly dba to set standards on the use of it.

Thanks,
John





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search