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:

  • How to split a single column values to multiple column values?
  • Named parameter query not behaving as hoped:
  • Find user who have use same IP
  • SQL SUM based on first two characters of one column, one table
  • How to get FKs between different fields in one table?
  • Migrating MOSS 2007 from SQL 2000 to SQL 2005

    My question is, where can I see in the system tables/views the Logins/Users that have been assigned to a given credential? Thanks!

  • How can I use TSQL to check when a user's password will expire?
  • 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

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.