I plan to migrate a SQL Server database to another server. There is a certificate listed under the Security > Certificates view in SSMS Object Explorer.

I ran the below query to check if database is encrypted and it is not...

select name, is_encrypted from sys.databases

How can I find if this certificate is used? And how do I ensure successful migration?


  • Hi there. I realize that I answered this almost 2 years after it was asked, but I was wondering if you had any feedback to provide. Thanks :). Jan 18, 2017 at 18:19
  • Hi, the queries below helped me but none of my databases were encrypted despite the presence of certs. Hope this is of some help.
    – K09
    Jan 31, 2017 at 12:29

2 Answers 2


As far as I know, the places where a Certificate will be linked -- whether by id or thumbprint -- can be found via the following three queries:

SELECT N'key_encryptions' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], ke.*
FROM   sys.certificates crt
INNER JOIN sys.key_encryptions ke
        ON ke.[thumbprint] = crt.[thumbprint]

SELECT N'database_mirroring_endpoints' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], dme.*
FROM   sys.certificates crt
INNER JOIN sys.database_mirroring_endpoints dme
        ON dme.[certificate_id] = crt.[certificate_id]

SELECT N'service_broker_endpoints' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], sbe.*
FROM   sys.certificates crt
INNER JOIN sys.service_broker_endpoints sbe
        ON sbe.[certificate_id] = crt.[certificate_id]

AND, the query shown in this answer:

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

You can migrate the Certificate by backing it (and its private key or the Database Master Key) up to files and then restoring those files into the new Database. Please see the MSDN documentation for the following commands:

And, if any Certificates are ENCRYPTED_BY_MASTER_KEY (or whatever), then you will likely also need to look at:


This will provide details about the certificates encrypting any database that has TDE encryption enabled:

use master 
    db_name(db.database_id) DatabaseName,
from sys.databases db
join sys.dm_database_encryption_keys dek
join sys.certificates c

For all other column level encryption within a user database, backup the Database Master Key, migrate it to the other environment and restore it after restoring the user database.

  • The OP knows that the database is not encrypted. Specifically asking for dbname --> security --> Certificates
    – Kin Shah
    Nov 18, 2016 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.