Export (0) Print
Expand All
Expand Minimize

ADD SIGNATURE (Transact-SQL)

Updated: 17 July 2006

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

Topic link icon Transact-SQL Syntax Conventions


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

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.

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.

ms181700.Caution(en-US,SQL.90).gifCaution:
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.

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.

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

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

Release History

17 July 2006

Changed content:
  • Corrected permissions requirement, added assemblies to list of signable entities, and corrected syntax.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft