USE master
go
-- Create a test login.
CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
go
-- Create test two databases
CREATE DATABASE db1
CREATE DATABASE db2
go
-- Move to first test database.
USE db1
go
-- Create certificate in db1
CREATE CERTIFICATE crossdbcert
   ENCRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds'
   WITH SUBJECT = 'Cross-db test',
   START_DATE = '20020101', EXPIRY_DATE = '20200101'
go
-- Get the certificate bytes into a temp table so we can use it in 
-- the target database.
CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL,
                    privkey varbinary(MAX) NOT NULL)
INSERT #keys (pubkey, privkey)
   SELECT certencoded(cert_id('crossdbcert')),
          certprivatekey(cert_id('crossdbcert'), 
	                 'She said She said',
                         'Lucy in the Skies with Diamonds')
go
-- Create the certificate user. Note that we do not grant access to
-- testuser.
CREATE USER certuser FROM CERTIFICATE crossdbcert
go
-- A test table.
CREATE TABLE testtbl (a int NOT NULL,
                      b int NOT NULL,
                      c int NOT NULL)
go
-- Insert some test data.
INSERT testtbl (a, b, c) VALUES (12, 23, 34)
go
-- The certificate user needs to access this table.
GRANT SELECT ON testtbl TO certuser
go
-- Switch to the second database.
USE db2
go
-- Welcome the test user to this database.
CREATE USER testuser
go
-- Signed test procedure.
CREATE PROCEDURE signed_sp AS
    SELECT a, b, c FROM db1..testtbl
go
-- Same code, but we will leave this one unsigned.
CREATE PROCEDURE unsigned_sp AS
    SELECT a, b, c FROM db1..testtbl
go
-- Give test user right to execute the procedures.
GRANT EXECUTE ON signed_sp TO testuser
GRANT EXECUTE ON unsigned_sp TO testuser
go
-- Import the certificate we created in the first test database into the second.
DECLARE @sql nvarchar(MAX)
SELECT @sql = 
   'CREATE CERTIFICATE crossdbcert 
    FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + '
    WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ',
                      DECRYPTION BY PASSWORD = ''She said She said'',
                      ENCRYPTION BY PASSWORD = ''Helter Skelter'')'
FROM #keys

PRINT @sql
EXEC (@sql)
DROP TABLE #keys
go
-- Delete the file with the certificate.
EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
go
-- Sign the test procedures.
ADD SIGNATURE TO signed_sp BY CERTIFICATE crossdbcert
    WITH PASSWORD = 'Helter Skelter'
go
-- Switch to the test login.
EXECUTE AS LOGIN = 'testuser'
go
-- Run the unsigned procedure. You will get a permission error.
EXEC unsigned_sp
go
-- Run the signed procedure. testuser can now access testdbl, even though
-- he is not a user of db1.
EXEC signed_sp
go
-- Back to ourselves.
REVERT
go
-- Clean up.
USE master
go
DROP DATABASE db1
DROP DATABASE db2
DROP LOGIN testuser