Signing Stored Procedures in SQL Server
You can sign a stored procedure with a certificate or an asymmetric key. This is designed for scenarios when permissions cannot be inherited through ownership chaining or when the ownership chain is broken, such as dynamic SQL. You then create a user mapped to the certificate, granting the certificate user permissions on the objects the stored procedure needs to access.
When the stored procedure is executed, SQL Server combines the permissions of the certificate user with those of the caller. Unlike the EXECUTE AS clause, it does not change the execution context of the procedure. Built-in functions that return login and user names return the name of the caller, not the certificate user name.
A digital signature is a data digest encrypted with the private key of the signer. The private key ensures that the digital signature is unique to its bearer or owner. You can sign stored procedures, functions, or triggers.
You can create a certificate in the master database to grant server-level permissions.
When you sign a stored procedure with a certificate, a data digest consisting of the encrypted hash of the stored procedure code is created using the private key. At run time, the data digest is decrypted with the public key and compared with the hash value of the stored procedure. Modifying the stored procedure invalidates the hash value so that the digital signature no longer matches. This prevents someone who does not have access to the private key from changing the stored procedure code. Therefore you must re-sign the procedure each time you modify it.
There are four steps involved in signing a module:
Create a certificate using the Transact-SQL CREATE CERTIFICATE [certificateName] statement. This statement has several options for setting a start and end date and a password. The default expiration date is one year
Create a database user associated with that certificate using the Transact-SQL CREATE USER [userName] FROM CERTIFICATE [certificateName] statement. This user exists in the database only and is not associated with a login.
Grant the certificate user the required permissions on the database objects.
A certificate cannot grant permissions to a user that has had permissions revoked using the DENY statement. DENY always takes precedence over GRANT, preventing the caller from inheriting permissions granted to the certificate user.
Sign the procedure with the certificate using the Transact-SQL ADD SIGNATURE TO [procedureName] BY CERTIFICATE [certificateName] statement.
For more information, see the following resources.
Module Signing in SQL Server Books Online
Describes module signing, providing a sample scenario and links to the relevant Transact-SQL topics.
Signing Stored Procedures with a Certificate in SQL Server Books Online
Provides a tutorial for signing a stored procedure with a certificate.
Securing ADO.NET Applications
Overview of SQL Server Security
Application Security Scenarios in SQL Server
Managing Permissions with Stored Procedures in SQL Server
Writing Secure Dynamic SQL in SQL Server
Customizing Permissions with Impersonation in SQL Server
Modifying Data with Stored Procedures
ADO.NET Managed Providers and DataSet Developer Center