CertProperty (Transact-SQL)
Returns the value of a specified certificate property.
Transact-SQL Syntax Conventions
- Cert_ID
-
Is the ID of the certificate. Cert_ID is an int.
- Expiry_Date
-
Is the date of expiration of the certificate.
- Start_Date
-
Is the date when the certificate becomes valid.
- Issuer_Name
-
Is the issuer name of the certificate.
- Cert_Serial_Number
-
Is the certificate serial number.
- Subject
-
Is the subject of the certificate.
- SID
-
Is the SID of the certificate. This is also the SID of any login or user mapped to this certificate.
- String_SID
-
Is the SID of the certificate as a character string. This is also the SID of any login or user mapped to the certificate.
The property specification must be enclosed in single quotation marks.
The return type depends on the property that is specified in the function call. All return values are wrapped in the return type of sql_variant.
-
Expiry_Date and Start_Date return datetime.
-
Cert_Serial_Number, Issuer_Name, Subject, and String_SID return nvarchar.
-
SID returns varbinary.
Information about certificates is visible in the sys.certificates catalog view.
The following example returns the certificate subject.
-- First create a certificate.
CREATE CERTIFICATE Marketing19 WITH
START_DATE = '04/04/2004' ,
EXPIRY_DATE = '07/07/2007' ,
SUBJECT = 'Marketing Print Division';
GO
-- Now use CertProperty to examine certificate
-- Marketing19's properties.
DECLARE @CertSubject sql_variant;
set @CertSubject = CertProperty( Cert_ID('Marketing19'), 'Subject');
PRINT CONVERT(nvarchar, @CertSubject);
GO
