CRw3BSZJ

1USE master;
2CREATE DATABASE db_a;
3ALTER DATABASE db_a SET DB_CHAINING ON;
4CREATE DATABASE db_b;
5ALTER DATABASE db_b SET DB_CHAINING ON;
6CREATE LOGIN db_a_login WITH PASSWORD = 'aasdfasdfasdf78723%';
7GO
8
9USE db_a;
10
11CREATE TABLE dbo.t
12(
13    someval varchar(10) NOT NULL
14);
15
16CREATE USER db_a_login FOR LOGIN db_a_login;
17
18USE db_b;
19
20CREATE TABLE dbo.t
21(
22    someval varchar(10) NOT NULL
23);
24
25CREATE USER db_a_login FOR LOGIN db_a_login;
26
27
28USE db_a;
29
30GO
31CREATE VIEW dbo.both_t
32AS
33SELECT *
34FROM db_a.dbo.t
35UNION ALL
36SELECT *
37FROM db_b.dbo.t;
38GO
39GRANT SELECT ON dbo.both_t TO db_a_login;
40GRANT SELECT ON dbo.t TO db_a_login;
41GO
42
43EXECUTE AS LOGIN = 'db_a_login';
44SELECT *
45FROM dbo.both_t;
46REVERT
47	
48ALTER DATABASE db_a SET DB_CHAINING OFF;
49ALTER DATABASE db_b SET DB_CHAINING OFF;
50
51EXECUTE AS LOGIN = 'db_a_login';
52SELECT *
53FROM dbo.both_t;
54REVERT
55	
56USE master;
57IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_a') 
58DROP DATABASE db_a;
59IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_b') 
60DROP DATABASE db_b;
61IF EXISTS (SELECT 1 FROM sys.server_principals sp WHERE sp.name = 'db_a_login') 
62DROP LOGIN db_a_login;
63GO