Cross-database execution permissions with certificates and schemas



I have run into an obstacle while implementing cross-database execution
permissions using certificates and schemas.

The scenario I'm working with is pretty simple, and I've got a formula that
works and a formula that doesn't (complete with scripts below). Can someone
take a look and help me out?

Database 1 (named [dispatcherDB]) has a stored procedure called [dispatch].
This stored procedure executes a procedure in database 2 (named [providerDB]).

I follow these steps to create everything and grant cross-database
permissions:
1. Executing as login='sa':
1.1 create a database [dispatcherDB], and a login [dispatcher],
1.2 create a second database [providerDB], and a login [provider],
1.3 in the [dispatcherDB] create a user [dispatcher],
1.4 make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner',
N'dispatcher')
2. Executing as user='dispatcher':
2.1 create a stored procedure [dispatch],
2.2 create a certificate,
2.3 sign the stored procedure (ADD SIGNATURE TO OBJECT::[dispatch] ...)
2.4 Discard the private key from the certificate,
2.5 Back up the certificate to a file.
3. Executing as login='sa':
3.1 in the [providerDB] create a user [provider] with a default schema [pvd]
3.2 make the provider user the owner (EXEC sp_addrolemember N'db_owner',
N'provider')
4. Executing as user='provider]':
4.1 create a schema [pvd]
4.2 create a certificate from the file.
4.3 Create a user from the certificate,
4.4 GRANT AUTHENTICATE TO the certificate user,
4.5 GRANT EXECUTE ON [providerDB].[pvd].[testProc] to the certificate user.
5. Executing as user='dispatcher'
5.1 execute [dispatch]

Everything works in this case.

But here's a description of the subtly different case (which is the
condition my application demands), which does not work:
1. Executing as login='sa':
1.1 create a database [dispatcherDB], and a login [dispatcher],
1.2 create a second database [providerDB], and a login [provider],
1.3 in the [dispatcherDB] create a user [dispatcher] *** with a default
schema [dsp] ***,
1.4 make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner',
N'dispatcher')
2. Executing as user='dispatcher':
2.0 *** create a schema [dsp] ***,
2.1 create a stored procedure [dispatch] (this time it is [dsp].[dispatch],
not [dbo].[dispatch]),
2.2 create a certificate,
2.3 sign the stored procedure (ADD SIGNATURE TO OBJECT::[dispatch] ...)
2.4 Discard the private key from the certificate,
2.5 Back up the certificate to a file.
3. Executing as login='sa':
3.1 in the [providerDB] create a user [provider] with a default schema [pvd]
3.2 make the provider user the owner (EXEC sp_addrolemember N'db_owner',
N'provider')
4. Executing as user='provider]':
4.1 create a schema [pvd]
4.2 create a certificate from the file.
4.3 Create a user from the certificate,
4.4 GRANT AUTHENTICATE TO the certificate user,
4.5 GRANT EXECUTE ON [providerDB].[pvd].[testProc] to the certificate user.
5. Executing as user='dispatcher'
5.1 execute [dispatch]

This time there's the error message:
Msg 916, Level 14, State 1, Procedure testProc, Line 10
The server principal "dispatcher" is not able to access the database
"providerDB" under the current security context.

Okay, there is a difference in the security context of the caller -- the
callee's context is the same in both scenarios.

Here's a look at the rows in sys.user_token in the scenario that works:
<user_token principal_id="1" name="dbo" type="SQL USER" usage="GRANT OR
DENY" />
<user_token principal_id="0" name="public" type="ROLE" usage="GRANT OR
DENY" />
<user_token principal_id="16384" name="db_owner" type="ROLE" usage="GRANT
OR DENY" />

Here's what is in sys.user_token in the scenario that doesn't work:
<user_token principal_id="5" name="dispatcher" type="SQL USER"
usage="GRANT OR DENY" />
<user_token principal_id="0" name="public" type="ROLE" usage="GRANT OR
DENY" />
<user_token principal_id="16384" name="db_owner" type="ROLE" usage="GRANT
OR DENY" />

Did I discover some magic property of the [dbo] user and [dbo] schema (not
likely), or am I missing something conceptual about permissions grants
through certificates?

Thanks,
--Craig.

Here's the script that works: (the script that doesn't follows)
-----
SELECT 0, SUSER_NAME(), USER_NAME()
GO
REVERT
GO
SELECT 1, SUSER_NAME(), USER_NAME()
GO
USE [master]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'dispatcherDB')
DROP DATABASE [dispatcherDB]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'providerDB') DROP
DATABASE [providerDB]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'dispatcher') DROP LOGIN [dispatcher]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'provider') DROP LOGIN [provider]
GO
EXECUTE xp_cmdshell 'del c:\temp\dispatchSproc.cert', NO_OUTPUT;
GO
CREATE DATABASE [dispatcherDB]
GO
CREATE DATABASE [providerDB]
GO
CREATE LOGIN [dispatcher]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE = [dispatcherDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
CREATE LOGIN [provider]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE=[providerDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
-- Create objects in dispatcher database
USE [dispatcherDB]
GO
CREATE USER [dispatcher]
-- WITH DEFAULT_SCHEMA = [dsp];
GO
EXEC sp_addrolemember N'db_owner', N'dispatcher'
GO
-- Continue to create objects as the dispatcher user, in the default schema
for this user, dsp:
EXECUTE AS USER = 'dispatcher'
GO
-- CREATE SCHEMA [dsp] AUTHORIZATION [dispatcher];
GO
CREATE PROCEDURE [dispatch]
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @lclRequest xml;
DECLARE @lclResponse xml;

SET @lclRequest = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Request')
);
SELECT @lclRequest;

EXECUTE [providerDB].[pvd].[testProc]
@inRequest = @lclRequest,
@outResponse = @lclResponse OUTPUT;

SELECT @lclResponse AS 'Response';
END;
GO
-- Drop existing certificate
IF EXISTS (
SELECT *
FROM [sys].[certificates]
WHERE [name] = 'dispatcherSproc'
) DROP CERTIFICATE [dispatchSproc];
GO
-- Create a certificate to sign this stored procedure
CREATE CERTIFICATE [dispatchSproc]
ENCRYPTION BY PASSWORD = '53cr37'
WITH SUBJECT = 'Cross-database permissions certificate',
START_DATE = '01/01/2008',
EXPIRY_DATE = '12/31/2018';
GO
-- Sign the service process' stored procedure with the certificate
ADD SIGNATURE TO OBJECT::[dispatch]
BY CERTIFICATE [dispatchSproc]
WITH PASSWORD = '53cr37';
GO
-- Discard the private key as a security measure
ALTER CERTIFICATE [dispatchSproc]
REMOVE PRIVATE KEY;
GO
BACKUP CERTIFICATE [dispatchSproc]
TO FILE = 'c:\temp\dispatchSproc.cert';
GO
-- back to sa:
REVERT
GO
SELECT 2, SUSER_NAME(), USER_NAME()
GO
-- Create objects in provider database
USE [providerDB]
GO
CREATE USER [provider]
WITH DEFAULT_SCHEMA = [pvd];
GO
EXEC sp_addrolemember N'db_owner', N'provider'
GO
-- Continue to create objects as the provider user, in the default schema
for this user, pvd:
EXECUTE AS USER = 'provider'
GO
CREATE SCHEMA [pvd] AUTHORIZATION [provider]
GO
-- Create a certificate from the file created for signing dispatcherDB's
stored procedures:
CREATE CERTIFICATE [dispatchSproc]
FROM FILE = 'c:\temp\dispatchSproc.cert';
GO
-- Create a user from the certificate
CREATE USER [dispatchSprocUser]
FROM CERTIFICATE [dispatchSproc];
GO
CREATE TABLE [test](
[testID] [bigint] IDENTITY(1,1) NOT NULL,
[request] [xml] NOT NULL,
[requester] [xml] NOT NULL,
[updatetime] [datetime] NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO
CREATE PROCEDURE [testProc]
@inRequest xml,
@outResponse xml OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @lclRequester xml;
SET @lclRequester = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Requester')
);

INSERT [test] (
request,
requester
) VALUES (
@inRequest,
@lclRequester
);

SET @outResponse = (
SELECT *
FROM [test]
FOR XML AUTO, ROOT('Response')
);
END;
GO
GRANT AUTHENTICATE TO [dispatchSprocUser];
GO
GRANT EXECUTE ON [testProc] TO [dispatchSprocUser];
GO
-- back to sa
REVERT
GO
SELECT 3, SUSER_NAME(), USER_NAME()
GO
USE [dispatcherDB]
GO
-- test as dispatcher login:
EXECUTE AS LOGIN = 'dispatcher'
GO
EXECUTE [dispatch]
GO
REVERT
GO

-----

Here's the script that doesn't work:

-----
SELECT 0, SUSER_NAME(), USER_NAME()
GO
REVERT
GO
SELECT 1, SUSER_NAME(), USER_NAME()
GO
USE [master]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'dispatcherDB')
DROP DATABASE [dispatcherDB]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'providerDB') DROP
DATABASE [providerDB]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'dispatcher') DROP LOGIN [dispatcher]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'provider') DROP LOGIN [provider]
GO
EXECUTE xp_cmdshell 'del c:\temp\dispatchSproc.cert', NO_OUTPUT;
GO
CREATE DATABASE [dispatcherDB]
GO
CREATE DATABASE [providerDB]
GO
CREATE LOGIN [dispatcher]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE = [dispatcherDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
CREATE LOGIN [provider]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE=[providerDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
-- Create objects in dispatcher database
USE [dispatcherDB]
GO
CREATE USER [dispatcher]
WITH DEFAULT_SCHEMA = [dsp];
GO
EXEC sp_addrolemember N'db_owner', N'dispatcher'
GO
-- Continue to create objects as the dispatcher user, in the default schema
for this user, dsp:
EXECUTE AS USER = 'dispatcher'
GO
CREATE SCHEMA [dsp] AUTHORIZATION [dispatcher];
GO
CREATE PROCEDURE [dispatch]
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @lclRequest xml;
DECLARE @lclResponse xml;

SET @lclRequest = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Request')
);
SELECT @lclRequest;

EXECUTE [providerDB].[pvd].[testProc]
@inRequest = @lclRequest,
@outResponse = @lclResponse OUTPUT;

SELECT @lclResponse AS 'Response';
END;
GO
-- Drop existing certificate
IF EXISTS (
SELECT *
FROM [sys].[certificates]
WHERE [name] = 'dispatcherSproc'
) DROP CERTIFICATE [dispatchSproc];
GO
-- Create a certificate to sign this stored procedure
CREATE CERTIFICATE [dispatchSproc]
ENCRYPTION BY PASSWORD = '53cr37'
WITH SUBJECT = 'Cross-database permissions certificate',
START_DATE = '01/01/2008',
EXPIRY_DATE = '12/31/2018';
GO
-- Sign the service process' stored procedure with the certificate
ADD SIGNATURE TO OBJECT::[dispatch]
BY CERTIFICATE [dispatchSproc]
WITH PASSWORD = '53cr37';
GO
-- Discard the private key as a security measure
ALTER CERTIFICATE [dispatchSproc]
REMOVE PRIVATE KEY;
GO
BACKUP CERTIFICATE [dispatchSproc]
TO FILE = 'c:\temp\dispatchSproc.cert';
GO
-- back to sa:
REVERT
GO
SELECT 2, SUSER_NAME(), USER_NAME()
GO
-- Create objects in provider database
USE [providerDB]
GO
CREATE USER [provider]
WITH DEFAULT_SCHEMA = [pvd];
GO
EXEC sp_addrolemember N'db_owner', N'provider'
GO
-- Continue to create objects as the provider user, in the default schema
for this user, pvd:
EXECUTE AS USER = 'provider'
GO
CREATE SCHEMA [pvd] AUTHORIZATION [provider]
GO
-- Create a certificate from the file created for signing dispatcherDB's
stored procedures:
CREATE CERTIFICATE [dispatchSproc]
FROM FILE = 'c:\temp\dispatchSproc.cert';
GO
-- Create a user from the certificate
CREATE USER [dispatchSprocUser]
FROM CERTIFICATE [dispatchSproc];
GO
CREATE TABLE [test](
[testID] [bigint] IDENTITY(1,1) NOT NULL,
[request] [xml] NOT NULL,
[requester] [xml] NOT NULL,
[updatetime] [datetime] NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO
CREATE PROCEDURE [testProc]
@inRequest xml,
@outResponse xml OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @lclRequester xml;
SET @lclRequester = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Requester')
);

INSERT [test] (
request,
requester
) VALUES (
@inRequest,
@lclRequester
);

SET @outResponse = (
SELECT *
FROM [test]
FOR XML AUTO, ROOT('Response')
);
END;
GO
GRANT AUTHENTICATE TO [dispatchSprocUser];
GO
GRANT EXECUTE ON [testProc] TO [dispatchSprocUser];
GO
-- back to sa
REVERT
GO
SELECT 3, SUSER_NAME(), USER_NAME()
GO
USE [dispatcherDB]
GO
-- test as dispatcher login:
EXECUTE AS LOGIN = 'dispatcher'
GO
EXECUTE [dispatch]
GO
REVERT
GO

-----
.