locked
Permission Error when using Cross-Database chaining RRS feed

  • Question

  • Hello,

    From a PowerPivot connection I'm getting a 'SELECT permission was denied on the object.. ' error.  Here's the relevant background.

    1. SQL Server Professional 2012
    2. One database contains the underlying table objects and data.
    3. Second database contains a view that is built on top of several of these tables.
    4. Active Directory group contains a set of domain users who have read permission.
    5. This group is set as a Login on the DB server.
    6.  On the second database (where the view object lives), I've set a Security Role, which has as a Member the Login (from Step 5.)
    7. The view (from Step 3.) is a Securable item in the Role (from Step 6.)  The Permission for this secured object is 'Select'.
    8. Both databases have their 'Enable Cross-Database Chaining' property set to True.

    Any help is greatly appreciated.

    Cap.

    Tuesday, January 20, 2015 10:28 PM

Answers

  • Fixed!

    The two databases had different owners.. gah, imagine that.. ownership chaining requires the same owner!! :)

    • Marked as answer by captkirk35 Wednesday, January 21, 2015 11:22 PM
    • Unmarked as answer by captkirk35 Wednesday, January 21, 2015 11:22 PM
    • Marked as answer by captkirk35 Wednesday, January 21, 2015 11:22 PM
    Wednesday, January 21, 2015 11:21 PM

All replies

  • Hello Cap,

    By default the Cross-Database chaining security option is disabled and this by good reason. So ownership chaining is not working here, you have to grant read permissions for the base tables in "database one" as well.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Michael Amadi Wednesday, January 21, 2015 9:05 AM
    Wednesday, January 21, 2015 8:11 AM
  • I've enabled the Ownership Chaining option on both databases, and this solution has worked for me in the past.  When it works, I have no need to set any permissions to underlying tables, since the outer database's view has the read only permission setting.

    Not sure what's going on.

    Wednesday, January 21, 2015 5:31 PM
  • The users need access to both databases, but SELECT only on the views.

    Put differently, cross database ownership chaining does not grant database access. It only suppresses permissions checking for users who already have access.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, January 21, 2015 5:39 PM
  • I've enabled the Ownership Chaining option on both databases,

    Cross database ownership chaining is a server not a database setting

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 21, 2015 5:47 PM
  • it can be configured on the whole instance or selectively

    https://msdn.microsoft.com/en-us/library/bb669059(v=vs.110).aspx

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 21, 2015 5:56 PM
  • The Login (the AD group) has membership in both databases' 'Public' group, which is what I've done in the past when this solution works.
    Wednesday, January 21, 2015 6:28 PM
  • We have the setting at the database not server level, per the best practice guidance we've followed.

    • Cross database ownership chaining should not be turned on at the server level. 
    • It should only be turned on for databases which require it.

    http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/

    Wednesday, January 21, 2015 6:30 PM
  • This works for me:

    --use master 
    --go
    --drop database a
    --drop database b
    --go
    
    create database a
    create database b
    go
    alter database a set db_chaining on
    alter database b set db_chaining on
    go 
    create login [dbrowne2\ReportUsers] from windows
    go
    use a
    go
    create user ReportUsers for login [dbrowne2\ReportUsers]
    go
    use b
    go
    create user ReportUsers for login [dbrowne2\ReportUsers]
    grant select to ReportUsers
    go
    use a
    create table t(id int)
    go
    use b
    go
    create view vt as select * from a.dbo.t 
    go
    

    Then add a user to that group, log on and connect.  You can connect to both databases, and select from the view in b, but not the table in a.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, January 21, 2015 8:48 PM
  • I have a couple of schemas in the mix.  Was thinking maybe the problem was related to that, but haven't been able to verify.

    Two schemas at source database:  Schema A, Schema B.  The underlying tables belong to one or the other.

    In the wrapper database, I tried adding those two Schemas to the Permission section, but it didn't help.  The calling view originally was in a third Schema (Schema C), but I've tried altering the schema to match the schema from the source, and that doesn't fix it.

    Wednesday, January 21, 2015 9:09 PM
  • Fixed!

    The two databases had different owners.. gah, imagine that.. ownership chaining requires the same owner!! :)

    • Marked as answer by captkirk35 Wednesday, January 21, 2015 11:22 PM
    • Unmarked as answer by captkirk35 Wednesday, January 21, 2015 11:22 PM
    • Marked as answer by captkirk35 Wednesday, January 21, 2015 11:22 PM
    Wednesday, January 21, 2015 11:21 PM