Signing Stored Procedures with a Certificate

This tutorial illustrates signing stored procedures using a certificate generated by SQL Server.

Signing stored procedures using a certificate is useful when you want to require permissions

on the stored procedure but you do not want to explicitly grant a user those rights. Although

you can accomplish this task in other ways, such as using the EXECUTE AS statement, using a

certificate allows you to use a trace to find the original caller of the stored procedure. This

provides a high level of auditing, especially during security or Data Definition Language (DDL)

operations.

You can create a certificate in the master database to allow server-level permissions, or you can

create a certificate in any user databases to allow database-level permissions. In this scenario, a

user with no rights to base tables must access a stored procedure in the

database, and you want to audit the object access trail. Rather than using other ownership

chain methods, you will create a server and database user account with no rights to the base

objects, and a database user account with rights to a table and a stored procedure. Both the

stored procedure and the second database user account will be secured with a certificate. The

second database account will have access to all objects, and grant access to the stored

procedure to the first database user account.

In this scenario you will first create a database certificate, a stored procedure, and a user, and

then you will test the process following these steps:

Each code block in this example is explained in line. To copy the complete example, see

Complete Example

at the end of this tutorial.

To complete this tutorial, you need SQL Server Management Studio, access to a server that’s

running SQL Server, and an AdventureWorks database.

７

Note

To run the code in this tutorial you must have both Mixed Mode security configured and

the

database installed.

AdventureWorks2022
AdventureWorks2022