Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ownership chaining Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2007 4:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 10:02 AM
Points: 1,547, 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




Post #399919
Posted Tuesday, September 18, 2007 7:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 10:02 AM
Points: 1,547, 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




Post #399962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse