Skip to content

certproperty-transact-sql.md

Latest commit

 

History

History
106 lines (83 loc) · 3.89 KB

certproperty-transact-sql.md

File metadata and controls

106 lines (83 loc) · 3.89 KB
title CERTPROPERTY (Transact-SQL)
description CERTPROPERTY (Transact-SQL)
author VanMSFT
ms.author vanto
ms.date 07/24/2017
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
CERTPROPERTY
CERTPROPERTY_TSQL
helpviewer_keywords
certificates [SQL Server], schema names
schemas [SQL Server], names
CERTPROPERTY function
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

CERTPROPERTY (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

Returns the value of a specified certificate property.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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
The certificate ID value, of data type int.

Expiry_Date
The certificate expiration date.

Start_Date
The date when the certificate becomes valid.

Issuer_Name
The name of the certificate issuer.

Cert_Serial_Number
The certificate serial number.

Subject
The certificate subject.

SID
The certificate SID. This is also the SID of any login or user mapped to this certificate.

String_SID
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

Single quotation marks must enclose the property specification.

The return type depends on the property specified in the function call. The return type sql_variant wraps all return values.

  • Expiry_Date and Start_Date return datetime.
  • Cert_Serial_Number, Issuer_Name, String_SID, and Subject all return nvarchar.
  • SID returns varbinary.

Remarks

See certificate information in the sys.certificates catalog view.

Permissions

Requires appropriate permission(s) on the certificate, and requires that the caller has not been denied VIEW permission on the certificate. See CREATE CERTIFICATE (Transact-SQL) and GRANT CERTIFICATE PERMISSIONS (Transact-SQL) for more information about certificate permissions.

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/2040' ,  
    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)