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.

Caution:
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.

A. Signing a stored procedure by using a certificate

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

B. Signing a stored procedure by using a signed BLOB

CREATE DATABASE TestSignature ;
GO
USE TestSignature ;
GO
-- Create a CERTIFICATE to sign the procedure.
CREATE CERTIFICATE cert_signature_demo 
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
    WITH SUBJECT = 'ADD SIGNATURE demo';
GO
-- Create a simple procedure.
CREATE PROC [sp_signature_demo]
AS
    PRINT 'This is the content of the procedure.' ;
GO
-- Sign the procedure.
ADD SIGNATURE TO [sp_signature_demo] 
    BY CERTIFICATE [cert_signature_demo] 
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y' ;
GO
-- Get the signature binary BLOB for the sp_signature_demo procedure.
SELECT cp.crypt_property
    FROM sys.crypt_properties AS cp
    JOIN sys.certificates AS cer
        ON cp.thumbprint = cer.thumbprint
    WHERE cer.name = 'cert_signature_demo' ;
GO

-- Drop the procedure so that a new version can be created.
DROP PROC [sp_signature_demo] ;
GO
-- Re-create the procedure by using the exact text including spaces.
CREATE PROC [sp_signature_demo]
AS
    PRINT 'This is the content of the procedure.' ;
GO
-- Add the signature. Use the signature BLOB obtained earlier.
ADD SIGNATURE TO [sp_signature_demo] 
    BY CERTIFICATE [cert_signature_demo]
    WITH SIGNATURE = 0x831F5530C86CC8ED606E5BC2720DA835351E46219A6D5DE9CE546297B88AEF3B6A7051891AF3EE7A68EAB37CD8380988B4C3F7469C8EABDD9579A2A5C507A4482905C2F24024FFB2F9BD7A953DD5E98470C4AA90CE83237739BB5FAE7BAC796E7710BDE291B03C43582F6F2D3B381F2102EEF8407731E01A51E24D808D54B373 ;
GO

Reference

Help and Information