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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply