How to query which users are using which certificates in SQL Server?

Question

I would like to know which users a certificate is used by. I tried to do this query but got no results:

SELECT 
    master.sys.sql_logins.name,
    sys.certificates.name
FROM 
    master.sys.sql_logins 
INNER JOIN 
    sys.certificates 
    ON master.sys.sql_logins.sid = sys.certificates.sid

Does anyone know how to do this?

Any help would be greatly appreciated.

Answer 1

That information is not exactly available and may be a security risk if it was, because essentially you'd need to know the public keys used with that certificate. This StackOverflow answer may be relevant and essentially agrees with my previous statement.

Taking a step back, these are the things you can derive though:

1. If your certificate was generated for a specific database, then all principals within that database are applicable to that certificate. You should be able to see which principals are mapped to the given database certificate by querying sys.database_principals WHERE type = 'C' as discussed here.

2. You should be able to get the principal of who owns a given certificate by using the principal_id to join to the sys.server_principals view to get the principal name.