CROSS DB OWNERSHIP CHAINING

--Enabling cross database ownership chaining at server  level
sp_configure 'cross db ownership chaining',1
GO
reconfigure
GO
select * from sys.configurations
GO

--Enabling cross database ownership chaining at database level.

alter database lapfdb
set db_chaining on
GO
select * from sys.databases
GO


--Viewing the owners of the objects of database

SELECT
    so.[name] AS [Object]
  , sch.[name] AS [Schema]
  , USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [OwnerUserName]
  , sp.NAME AS [OwnerLoginName]
  , so.type_desc AS [ObjectType]
FROM sys.objects so
  JOIN sys.schemas sch
    ON so.[schema_id] = sch.[schema_id]
  JOIN [sys].database_principals dp
    ON dp.[principal_id] = COALESCE(so.[principal_id], sch.[principal_id])
  LEFT JOIN [master].[sys].[server_principals] sp
    ON dp.sid = sp.sid
WHERE so.[type] IN ('U', 'P');

--Working with creoss database ownership chaining

USE MASTER;
GO

CREATE DATABASE DemoDatabaseA;
GO

CREATE DATABASE DemoDatabaseB
GO

USE DemoDatabaseA
GO

EXEC sp_changedbowner 'sa';
GO

CREATE TABLE dbo.ATable (TableID INT);
GO

USE DemoDatabaseB
GO

EXEC sp_changedbowner 'sa';
GO


CREATE PROC dbo.QueryATable
AS
BEGIN
  SELECT TableID FROM DemoDatabaseA.dbo.ATable;
END;
GO


--Create a user in BD2

USE [master]
GO
CREATE LOGIN [new] WITH PASSWORD=N'Pa$$w0rd'
GO
USE [DemoDatabaseB]
GO
CREATE USER [new] FOR LOGIN [new]
GO

--Granting execute permission on the procrdure

GRANT EXECUTE ON dbo.QueryATable TO new
GO

--enable GUEST of DemoDatabaseA
use DemoDatabaseA
GO
grant connect to guest
-- Attempt to use QueryATable
USE DemoDatabaseB
GO

EXECUTE AS user = 'new';
GO

EXEC dbo.QueryATable;
GO

select * from fn_my_permissions(null,'object')

SELECT DISTINCT class_desc FROM fn_builtin_permissions(default)
    ORDER BY class_desc
 
REVERT;
GO


-- Turn on Cross-Database Ownership Chaining
-- Clean-up
USE MASTER;
GO

DROP DATABASE DemoDatabaseA
GO

DROP DATABASE DemoDatabaseB
GO

exec sp_droplogin 'new'
GO