Stored procedure security with execute as, cross database queries, and module signing

I have a situation that, while I was able to work around it (as the repro will show), I don't understand. Here are the high points

• Two databases, ChainingSource and ChainDestination, both of which have cross database chaining set to true

• A stored procedure in ChainingSource accesses, through an EXEC(@sql), accesses a table in ChainingDestination

• The stored procedure is defined with an execute as clause

• If I try to execute the procedure as is, it says the server principal of the execution context is unable to access ChainingDestination

• So I add a certificate and code signing into the mix. That is, I add a certificate mapped login to the server, mapped user to each of the databases, and grant permissions to the certificate mapped user accordingly

• If I leave the execute as clause in place, I get the same error.

• If I remove the execute as clause, everything is fine.

It's the second-to-last point that I'm confused about. Or, specifically, why that one doesn't work and the last one does.

```
/

Setup

/
USE [master];
go
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingSource')
BEGIN
ALTER DATABASE [ChainingSource] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ChainingSource] SET ONLINE;
DROP DATABASE [ChainingSource];
END
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingDestination')
BEGIN
ALTER DATABASE [ChainingDestination] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ChainingDestination] SET ONLINE;
DROP DATABASE [ChainingDestination];
END
GO

EXECUTE AS LOGIN = 'sa';
CREATE DATABASE [ChainingSource];
CREATE DATABASE [ChainingDestination];
GO
REVERT;
GO

ALTER DATABASE [ChainingSource] SET DB_CHAINING ON;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON;

IF SUSER_ID('myAppUser') IS null
CREATE LOGIN [myAppUser] WITH password = 'p@ssw0rd!23';

IF SUSER_ID('myAppUserEscalated') IS nul

You were headed in the right direction and were very close. Now you just need to view the module signing as a replacement of EXECUTE AS instead of as something that is added to it. Removing EXECUTE AS and myAppUserEscalated entirely, and relying solely upon module signing (and the certificate-based Login and related Users) allows cross-DB permissions and maintaining permissions when ownership-chaining doesn't work (i.e. Dynamic SQL), all while keeping TRUSTWORTHY set to OFF (and even keeping DB_CHAINING set to OFF).

Below is a test script that is based on the script in the question, but modified to start with the minimum options (i.e. DB_CHAINING not turned on, and the Certificate and Certificate-based Login / Users not created). It also has 4 stored procedures to easily test the various combinations of:

• Default (No Impersonation and No Dynamic SQL)

• Impersonation (but no Dynamic SQL)

• Dynamic SQL (but no Impersonation)

• Impersonation and Dynamic SQL

There are six tests in the script:

-
Test 1 shows that by default, none of the combinations works. The stored procedure, getSecrets (no Impersonation or Dynamic SQL), gets farther than the straight SQL due to ownership chaining, but cannot access the other DB due to no Cross-DB Ownership Chaining. The two stored procedures with Dynamic SQL get the same error as the straight SQL due to the Dynamic SQL breaking the ownership-chain.

-
Test 2 shows that when only DB_CHAINING is set to ON, then the stored procedure, getSecrets (no Impersonation or Dynamic SQL), works as desired between Databases. But the getSecretsWithDynamicSql stored procedure fails due to the Dynamic SQL breaking the ownership-chain, hence it cannot benefit from the Cross-DB Ownership Chaining.

-
Test 3 shows that when only TRUSTWORTHY is set to ON (just for the "Source" DB), then code using Impersonation (i.e. EXECUTE AS), with or without Dynamic SQL, works as desired between Databases. But code not using Impersonation doesn't work, same as in Test 1. Of course, we don't want TRUSTWORTHY to be ON as it is a security risk. This test is just to show how things used to be, prior to module signing (i.e. that TRUSTWORTHY is required when using Impersonation, which in turn is required when using Dynamic SQL).

-
Test 4 shows that when both DB_CHAINING and TRUSTWORTHY are set to ON, then code not using Dynamic SQL works without needing Impersonation, and any code using Impersonation, whether or not there is Dynamic SQL, works as desired between Databases. But again, we don't want TRUSTWORTHY to be ON as it is a security risk. This test is just to show how things used to be, prior to module signing.

-
Test 5 turns DB_CHAINING and TRUSTWORTHY back to OFF, creates the Certificate and the associated Login and Users, and signs the two stored procedures that are not using Impersonation (because there is no longer a need to use Impersonation). Both of the signed stored procedures work as intended :-).

-
Test 6 removes the two stored procedures that used Impersonation, and even removes the "Escalated" Login and associated Users that were being impersonated. Running Test 5 again proves that all that is needed is the module signing (which is why it is the super-cool way of controlling permissions :-).

The test script:
`/

Setup

/

/ CLEANUP **/

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'ChainingSource')
BEGIN
PRINT 'Dropping [ChainingSource] DB...';
ALTER DATABASE [ChainingSource] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ChainingSource] SET ONLINE;
DROP DATABASE [ChainingSource];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'ChainingDestination')
BEGIN
PRINT 'Dropping [ChainingDestination] DB...';
ALTER DATABASE [ChainingDestination] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ChainingDestination] SET ONLINE;
DROP DATABASE [ChainingDestination];
END;

IF (SUSER_ID(N'myAppUser') IS NOT NULL)
BEGIN
PRINT 'Dropping [myAppUser] Login...';
DROP LOGIN [myAppUser];
END;

IF (SUSER_ID(N'myAppUserEscalated') IS NOT NULL)
BEGIN
PRINT 'Dropping [myAppUserEscalated] Login...';
DROP LOGIN [myAppUserEscalated];
END;
GO

/ CREATE **/

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [ChainingSource] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [ChainingDestination] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

-- Set up Login/User: [myAppUser]
IF (SUSER_ID(N'myAppUser') IS NULL)
BEGIN
EXEC(N'
PRINT ''Creating [myAppUser]...'';
USE [master];
CREATE LOGIN [myAppUser] WITH PASSWORD = N''p@ssw0rd!23'';

USE [ChainingDestination];
CREATE USER [myAppUser];

USE [ChainingSour

StackExchange Database Administrators Q#152931, answer score: 8