Share via

ADD SIGNATURE (Transact-SQL)

  • Article

Adds a digital signature to a stored procedure, function, assembly, or trigger.

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Arguments

  • module_class
    Is the class of the module to which the signature is added. The default for schema-scoped modules is OBJECT.
  • module_name
    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.

Remarks

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.

Warning

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.

Permissions

Requires ALTER permission on the object and CONTROL permission on the certificate or asymmetric key. If an associated private key is protected by a password, the user also must have the password.

Examples

The following example signs the stored procedure HumanResources.uspUpdateEmployeeLogin with the certificate HumanResourcesDP.

USE AdventureWorks;
ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin 
    BY CERTIFICATE HumanResourcesDP;
GO

See Also

Reference

sys.crypt_properties (Transact-SQL)
DROP SIGNATURE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Corrected permissions requirement, added assemblies to list of signable entities, and corrected syntax.