SQL Server – How to list logins assigned to a credential?
A SQL Server credential is used to allow SQL Server User (not Windows User) to have an identity outside of the SQL Server and to enable them performing various tasks.
From the http://technet.microsoft.com/en-us/library/ms189828.aspx site, credential can be added to a (SQL Server) Login as follows:
ALTER LOGIN Mary5 ADD CREDENTIAL EKMProvider1; GO
My question is, where can I see in the system tables/views the Logins/Users that have been assigned to a given credential? Thanks!
2 Solutions collect form web for “SQL Server – How to list logins assigned to a credential?”
I think something similar to this should work for you.
SELECT sp.name FROM sys.server_principals AS sp INNER JOIN sys.credentials AS c ON sp.credential_id = c.credential_id WHERE c.name = 'YOUR_CREDENTIAL_NAME';
Here is more information on the sys.credentials and sys.server_principals catalog views:
Having searched for the answer to the same question and found a useful method which contains mapping between logins and credentials.
Here is my solution:
SELECT c.name FROM sys.server_principal_credentials pc INNER JOIN sys.credentials c ON pc.credential_id = c.credential_id WHERE principal_id IN ( SELECT principal_id FROM sys.server_principals WHERE name = ORIGINAL_LOGIN() )
@@VERSION = SQL Server 2008 R2