Post reply

ownership chaining

  • September 18, 2007 at 4:40 am

    #177485

    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

  • September 18, 2007 at 7:09 am

    #734289

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply