SQL Server 2008 R2 Cross Database Ownership Chaining Not working?

I have restored two SQL Server 2005 dbs (DB1 & DB2) to a new box running SQL Server 2008 R2.

All objects are owned by dbo

I have a stored procedure DB1.dbo.mp_SPTest. I have given execute permissions to SQLUser1.

CREATE PROCEDURE mp_SPTest
AS
SELECT DB2.dbo.mf_UserHasAccess("BasicUser", "bob")

mp_SPTest calls a scalar function in DB2 DB2.dbo.mf_UserHasAccess(), this function checks if the username passed is a member of a SQL Role.....

CREATE FUNCTION [dbo].[mf_UserHasAccess] (@RoleName varchar(50), @UserName varchar(128))  
RETURNS bit
AS  
BEGIN 
    DECLARE @Result bit

    SELECT @Result = 1
    WHERE @RoleName IN (
    SELECT CASE
        WHEN (usg.uid is null) THEN 'public'
        ELSE usg.name
        END AS RoleName
    FROM    dbo.sysusers usu
    LEFT OUTER JOIN (dbo.sysmembers mem 
         INNER JOIN dbo.sysusers usg 
         ON mem.groupuid = usg.uid)
    ON  usu.uid = mem.member开发者_Python百科uid
    LEFT OUTER JOIN master.dbo.syslogins lo 
    ON  usu.sid = lo.sid
    WHERE   
        (usu.islogin = 1 AND usu.isaliased = 0 AND usu.hasdbaccess = 1)
    AND (usg.issqlrole = 1 OR usg.uid is NULL)
    AND usu.name = @UserName)

    IF @Result <> 1
    BEGIN
        SET @Result = 0
    END

    RETURN @Result
END

When I run this procedure as "SQLUser1" it tells me that bob is not a member of BasicUser but when I run it as "sa" it tells me that he IS a member.

As I understand it... because both procedure and function are owned by dbo then that is the context that the function in test2 db would run, therefore it should have access to the same user and login tables.

This worked fine on SQL Server 2005, cant figure it out.

Hope this makes sense, thanks in advance.

Most likely the old SQL Server 2005 had the cross db ownership chaining option turned on, while the new SQL Server 2008 R2 instance has the option left at its default value (off).

But your assumption that 'dbo' in DB1 equate to 'dbo' in DB2 is wrong. 'dbo' in DB1 is the login who corresponds to the owner_sid of DB1 in sys.databases. 'dbo' in DB2 is, likewise, the login that corrsponds to the onwer_sid in sys.databases for DB2. If the two logins are different (if the owner_sid of the two databases is different) then very likely 'dbo' of DB1 will map to some other user and the ownership chain is broken, even if enabled to cross databases. Running ALTER AUTHORIZATION ON DATABASE::[DB..] TO [sa] would fix this problem (ie. it would force the owner_sid to match).

And finally, what you're doing is fundamentally flawed, as it relies on activating ownership chaining across databases, which is a huge security hole, see Potential Threats. A much better avenue is to use code signing.

I solved this identical problem by making the schema the view or procedure is running under the owner of the schemas in the databases it needs to access.

USE [TargetDB]

ALTER AUTHORIZATION ON SCHEMA::[TargetSchema] TO [SourceSchema]

For example USE [DB1]

ALTER AUTHORIZATION ON SCHEMA::[mem] TO [dbo] GO

Would allow a view run as DB2.DBO.view assuming chaining is turned on, to access a table in DB1.mem.table. Essentially cross db chaining causes it to access the target DB AS the schema the view is under, not the user who owns the database.