ADD SIGNATURE (Transact-SQL)
Adds a digital signature to a stored procedure, function, assembly, or trigger.
ADD [ COUNTER ] SIGNATURE TO module_class::module_name BY <crypto_list> [ ,...n ] <crypto_list> ::= CERTIFICATE cert_name | CERTIFICATE cert_name [ WITH PASSWORD = 'password' ] | CERTIFICATE cert_name WITH SIGNATURE = signed_blob | ASYMMETRIC KEY Asym_Key_Name | ASYMMETRIC KEY Asym_Key_Name [ WITH PASSWORD = 'password'] | ASYMMETRIC KEY Asym_Key_Name WITH SIGNATURE = signed_blob
Is the class of the module to which the signature is added. The default for schema-scoped modules is OBJECT.
Is the name of a stored procedure, function, assembly, or trigger to be signed or countersigned.
- CERTIFICATE cert_name
Is the name of a certificate with which to sign or countersign the stored procedure, function, assembly, or trigger.
- WITH PASSWORD = 'password'
Is the password that is required to decrypt the private key of the certificate or asymmetric key. This clause is only required if the private key is not protected by the database master key.
- SIGNATURE = signed_blob
Specifies the signed, binary large object (BLOB) of the module. This clause is useful if you want to ship a module without shipping the private key. When you use this clause, only the module, signature, and public key are required to add the signed binary large object to a database. signed_blob is the blob itself in hexadecimal format.
- ASYMMETRIC KEY Asym_Key_Name
Is the name of an asymmetric key with which to sign or counter-sign the stored procedure, function, assembly, or trigger.
The module being signed or counter-signed and the certificate or asymmetric key used to sign it must already exist. Every character in the module is included in the signature calculation. This includes leading carriage returns and line feeds.
A module can be signed and counter-signed by any number of certificates and asymmetric keys.
The signature of a module is dropped when the module is changed.
If a module contains an EXECUTE AS clause, the security ID (SID) of the principal is also included as a part of the signing process.
|Module signing should only be used to grant permissions, never to deny or revoke permissions.|
Information about signatures is visible in the sys.crypt_properties catalog view.
The following example signs the stored procedure
HumanResources.uspUpdateEmployeeLogin with the certificate
USE AdventureWorks; ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin BY CERTIFICATE HumanResourcesDP; GO