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.
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:
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
WHERE type = 'C'as discussed here.
You should be able to get the principal of who owns a given certificate by using the
principal_idto join to the
sys.server_principalsview to get the principal
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂