Echoes from the field 6: Controlling stored procedure execution context (Part 2)

Echoes from the field 6: Controlling stored procedure execution context (Part 2)

This second article in a two-part series shows you how to control the execution context of stored procedures. This time, it’s by using digital signatures and code signing.

During a recent consulting engagement, I noticed that the client needed to control the security context a stored procedure was running under but was using a convoluted method to do so. Changing the security context that stored procedures run under is a common requirement, letting users execute code via stored procedures that they aren’t allowed to execute directly.

There are two basic ways to control the execution context—and through it, the security context—of a stored procedure. In the previous article of this two-part series, we looked at the use of the EXECUTE AS clause.

In this second article, let’s explore the use of digital signatures and code signing to change and control security context.

Security Contexts

As noted in the last article, when a user is executing code in a database, a set of tokens is associated with that database user. If the database user is associated with a login, a set of tokens is also associated with the login. You can see these tokens by executing the following code:

SELECT * FROM sys.user_token;
SELECT * FROM sys.login_token;

We might, however, want the user to have additional permissions but only for the duration of the execution of a stored procedure. We can achieve this by the use of code signing.

To get started, the code below creates a database called Signing, to copy some data into the database from the AdventureWorks database, and to create a login and user called Test1.

USE master;
GO

CREATE DATABASE Signing;
GO

CREATE LOGIN Test1 WITH PASSWORD = N'Test1', CHECK_POLICY = OFF;
GO

USE Signing;
GO

SELECT * INTO dbo.WorkOrderRouting 
  FROM AdventureWorks.Production.WorkOrderRouting;
GO

CREATE USER Test1 FOR LOGIN Test1;
GO

Creating the Stored Procedure

First, let’s create a stored procedure that achieves two purposes. It will display the details of the execution context for the user that is executing the procedure and it will also try to access the data in the dbo.WorkOrderRouting table that we created previously. The code to do this is below:

USE Signing;
GO

CREATE PROCEDURE dbo.TestProc
AS 
BEGIN 
    SELECT SYSTEM_USER AS SystemUser,
           USER AS DatabaseUser,
           NAME AS ExecutionContext,
           TYPE AS Type, 
           USAGE AS Usage
    FROM sys.user_token; 

    SELECT * 
    FROM dbo.WorkOrderRouting
    ORDER BY ActualCost;
END;
GO

Signing the Stored Procedure

Before we can digitally sign a stored procedure, we need to create a certificate that can be used to sign it. Below, we create a new certificate from within SQL Server:

CREATE CERTIFICATE AccessCert
    ENCRYPTION BY PASSWORD = 'VerySecretStuff' 
    WITH SUBJECT = 'Gregs Test Cert', 
    EXPIRY_DATE = '20300101'; 
GO

Once we have a certificate, we can use it to digitally sign the stored procedure. We do that with the ADD SIGNATURE statement:

ADD SIGNATURE TO dbo.TestProc 
    BY CERTIFICATE AccessCert
    WITH PASSWORD = 'VerySecretStuff';
GO

Certificates are identified by a thumbprint. Next, we locate the thumbprint of the AccessCert by querying the sys.certificates view and then use it to find objects that have been signed with that certificate by querying the sys.crypt_properties view.

DECLARE @ThumbPrint VARBINARY(32);

SELECT @ThumbPrint = thumbprint 
FROM sys.certificates 
WHERE name = 'AccessCert';

SELECT OBJECT_NAME(major_id) AS ObjectName,
       crypt_property AS Signature
FROM sys.crypt_properties 
WHERE thumbprint = @ThumbPrint;

Assigning Permissions to the Certificate

SQL Server can create a database user from a certificate. Below, you can see the creation of a user and the assignment of permissions to that user. Note however, that this database user is not associated with any login. It’s just used as a container to hold permissions:

CREATE USER CertUser 
    FROM CERTIFICATE AccessCert;
GO 

GRANT SELECT ON dbo.WorkOrderRouting
    TO CertUser;
GO

GRANT EXECUTE ON dbo.TestProc
    TO Test1;
GO

Testing the Permissions

Now let’s see what happens when we execute the procedure. Below, we change our execution context to execute as the Test1 user, run the procedure and then revert back to our original context:

EXECUTE AS LOGIN = 'Test1';

EXEC dbo.TestProc;

REVERT;

When this code is executed, it returns the output as shown:

Note that the user tokens during the stored procedure execution include the tokens that the user would normally have plus the ones associated with the mapped certificate. The user then has their normal permissions plus any permissions that have been assigned to the user that is mapped to the certificate. That is why the user also had access to the data in the table.

Code Tampering

As well as providing a method for providing a user with permissions during the execution of a stored procedure, digital signing of a stored procedure protects it from tampering. Here, the stored procedure is modified:

ALTER PROC dbo.TestProc
AS
    PRINT 'Do something evil instead';
GO

Once the stored procedure is modified, the digital signature on the stored procedure is removed. If we again execute the code to check which objects are signed with the AccessCert certificate, no rows are returned. The signature has been removed, along with the additional permissions that it provided.

Encryption training

You should learn about how encryption and secrets work in SQL Server, as well as more about general security. You can do that with our course SQL Server Encryption for Developers and DBAs

2025-11-10