How to Drop a Certificate Conditionally with CERT_ID

2014-05-09 - Cryptography, General, Security

Introduction

Sometimes you need to be able to determine if a particular certificate exists already. For example, automated deployment scripts are a place where this functionality is commonly required. You could use the sys.certificates catalog view to get that information. But there is an even simpler way: CERT_ID().

CERT_ID() Example

The built-in CERT_ID() function takes one parameter, the name of the certificate. Its primary function is to return SQL Server's internal certificate_id for the certificate specified in the name parameter:

[sql] SELECT CERT_ID('ACertificate');
[/sql]

The output of this function is an integer value:

CERT_ID in Action.

This integer value represents the certificate_id, if a certificate with that name exists in the current database. Otherwise, NULL is returned. That fact however we can use to check for existence.

If we want to drop a certificate automatically if it exists, for example as part of a deployment script, we can use CERT_ID in an IF statement like this:

[sql] IF(CERT_ID('ACertificate') IS NOT NULL) DROP CERTIFICATE ACertificate;
[/sql]

If the certificate ACertificate exists, CERT_ID returns a non-NULL value and the DROP statement is executed. If it does not exist, the DROP statement is skipped.

Summary

SQL Server's built-in CERT_ID() function returns the internal certificate_id of a certificate in the current database. Since it returns NULL if the specified certificate does not exist, it can be used to execute statements conditionally based on the existence of a certificate.

Categories: Cryptography, General, Security
Tags: , , , , ,

One Response to How to Drop a Certificate Conditionally with CERT_ID

  1. Pingback: CERTPROPERTY – Digging Deeper into SQL Server Certificates - sqlity.net

Leave a Reply