cross database permission RRS feed

  • Question

  • dear all
        I got a problem when I set up my database:

        database1 has a table called table1
        database1 has a user called user1 having no explicit permission with table1
        user1 is mapped to login1,which does not belong to any fix server role

        database2 has a stored procedure call sp2
        database2 has a user called user1 having EXECUTE permission with sp2
        database2 has a table called table2
        user1 in database2 has no  explicit permission with table2
        user1 is mapped to login1 as well

       I found:

       it is successful,when I want to execute sp2 as:
       select * from database2.dbo.table2

      it failed, when I want to execute sp2 as:
      select * from database1.dbo.table1

     error message:The SELECT permission was denied on the object

     I didn't grant user2 select permission, but execute the sp2, why it works?

    when sp2 wants to select table1 in database1 why it doesn't work?

    any idea. many thanks


    Tuesday, November 17, 2009 5:50 PM


All replies

  • The database 2 scenario works as the user has EXEC permission for that stored proc which runs the sp and select works. But the database 1 scenario doesn’t work as you don’t have any defined privs like select / execute for the user. What are the other database roles that users have on the 2 different databases ?

    Thanks, Leks
    Tuesday, November 17, 2009 5:56 PM
  • "I didn't grant user2 select permission, but execute the sp2, why it works?"
    You granted user1 Execute permission for SP - that's why it works. User is not selecting data from Table2 - SP selects data and passes to the user.

    "when sp2 wants to select table1 in database1 why it doesn't work?"
    It won't work unless you grant user1 SELECT permission. Your user can execute SP, but since SP is selecting from another DB SQL Server is checking user1 permissions to select from table1.

    You can do a simple test:
    Login to your SQL Server Management Studio with user1. Expand DB1 and DB2 (tables). You won't see Table1 and Table2 (or any other tables you don't have access to) in both databases because user1 doesn't have any permissions except for login. You will see your SPs though.

    In order to query Table1 in DB1 you'd have to either create SP in DB1 or give user1 select permission.

    Hope that helps,
    Tuesday, November 17, 2009 7:06 PM
  •   For the scenario where the SP and the table are on the same DB, it works because of Ownership Chains. In a nutshell, permission checks to the table are being bypassed because: a) SP & table are owned by the same user, b) the operation performed is DML (SELECT) and c) the caller user1 has permission to execute the SP.  The second scenario failed because “Cross DB ownership chains” (CDOC) are disabled by default (BTW. It is highly recommended to not enable CDOC).

      Besides the BOL links I added I would like to recommend a couple of articles on this subject:

    ·        Basic SQL Server Security concepts - ownership chaining: good and evil; schemas

    ·         Using a digital signature as a secondary identity to replace Cross database ownership chaining

     I hope this information helps.
     -Raul Garcia
      SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, November 17, 2009 8:56 PM
  • Hello guys

    Thank all of you for your help.

    I've used cdoc to figure it out.

    This is a good place. Your replies are highly appreciated

    Friday, November 20, 2009 11:04 AM