sp_configure 'cross db ownership chaining', 1
GO
RECONFIGURE
GO

USE AdventureWorks
GO

CREATE DATABASE CustomerData
WITH DB_CHAINING ON

CREATE DATABASE MailingList
WITH DB_CHAINING ON
GO
CREATE LOGIN Daniel WITH PASSWORD = '?sdj7JS3&*(%sdp_';

USE CustomerData
CREATE TABLE Customers
(
   CustomerID int IDENTITY PRIMARY KEY,
   FirstName nvarchar(255) NOT NULL,
   LastName nvarchar(255) NOT NULL,
   Email varchar(255) NOT NULL
);

INSERT INTO Customers VALUES ('Jan', 'Kowalski', 'Jan.Kowalski@gdzies.com');
INSERT INTO Customers VALUES ('Janina', 'Jankowska', 'JaninaJ@gdziesindziej.com');
GO

CREATE USER William FOR LOGIN Daniel;
GO

USE MailingList
CREATE TABLE EmailAddresses
(
   ContactID int IDENTITY PRIMARY KEY,
   Email varchar(255) NOT NULL
);

INSERT INTO EmailAddresses VALUES('tgorski@domena.com');
INSERT INTO EmailAddresses VALUES('mfiszer@domena.com');
GO

CREATE VIEW vGetAllContactEmails
AS
SELECT Email FROM EmailAddresses
UNION
SELECT Email FROM CustomerData.dbo.Customers;
GO

CREATE USER Daniel FOR LOGIN Daniel;
GRANT SELECT ON vGetAllContactEmails TO Daniel;
GO

SETUSER 'Daniel'
SELECT * FROM vGetAllContactEmails
SETUSER

SETUSER 'Daniel'
SELECT Email FROM EmailAddresses
UNION
SELECT Email FROM CustomerData.dbo.Customers;
SETUSER
