Skip to content

Latest commit

 

History

History
110 lines (87 loc) · 3.78 KB

certproperty-transact-sql.md

File metadata and controls

110 lines (87 loc) · 3.78 KB
title ms.custom ms.date ms.prod ms.reviewer ms.suite ms.technology ms.tgt_pltfrm ms.topic f1_keywords dev_langs helpviewer_keywords ms.assetid caps.latest.revision author ms.author manager
CERTPROPERTY (Transact-SQL) | Microsoft Docs
07/24/2017
sql-non-specified
database-engine
language-reference
CERTPROPERTY
CERTPROPERTY_TSQL
TSQL
certificates [SQL Server], schema names
schemas [SQL Server], names
CERTPROPERTY function
966c09aa-bc4e-45b0-ba53-c8381871f638
22
BYHAM
rickbyh
jhubbard

CERTPROPERTY (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx_md]

Returns the value of a specified certificate property.

Topic link icon Transact-SQL Syntax Conventions

Syntax

CertProperty ( Cert_ID , '<PropertyName>' )  
  
<PropertyName> ::=  
   Expiry_Date | Start_Date | Issuer_Name   
   | Cert_Serial_Number | Subject | SID | String_SID

Arguments

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.

Return types

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.

Remarks

Information about certificates is visible in the sys.certificates catalog view.

Permissions

Requires some permission on the certificate and that the caller has not been denied VIEW DEFINITION permission on the certificate.

Examples

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

See also

CREATE CERTIFICATE (Transact-SQL)
ALTER CERTIFICATE (Transact-SQL)
CERT_ID (Transact-SQL) Encryption Hierarchy sys.certificates (Transact-SQL) Security Catalog Views (Transact-SQL)