locked
selecting credential that are mapped to SQL login RRS feed

  • Question

  • Hi,

    is any way to select SQL logins that are Mapped To Credentials as using this query I get only NULL:

    SELECT credential_id FROM sys.server_principals WHERE name = 'test_login'

    I see the list of Mapped Credentials in SQL login properties with GUI, but can not select this option. 

    Thanks

    Monday, November 23, 2015 10:02 AM

Answers

  • Found sys table for this:

    sys.server_principal_credentials

    • Marked as answer by jori5 Tuesday, November 24, 2015 8:49 AM
    Tuesday, November 24, 2015 8:49 AM

All replies

  • Hi Jori.

    Thank you for posting on MSDN forum.

    ID of a credential associated with this principal. If no credential is associated with this principal, credential_id will be NULL.

    Please try below script,

    SELECT pr.principal_id, pr.name, pr.type_desc, 
        pe.state_desc, pe.permission_name 
    FROM sys.server_principals AS pr 
    JOIN sys.server_permissions AS pe 
        ON pe.grantee_principal_id = pr.principal_id;

    I would suggest please have a look into below MDSN article for help,

    https://msdn.microsoft.com/en-us/library/ms188786.aspx?f=255&MSPPError=-2147217396

    https://technet.microsoft.com/en-us/library/ms188786(v=sql.105).aspx


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Monday, November 23, 2015 10:33 AM
  • Hi,

    Try this catalog view

    SELECT * FROM sys.credentials


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, November 23, 2015 10:42 AM
  • 		SELECT pr.principal_id, pr.name, pr.type_desc, 
        pe.state_desc, pe.permission_name 
    FROM sys.server_principals AS pr 
    JOIN sys.server_permissions AS pe 
        ON pe.grantee_principal_id = pr.principal_id;

    This one just for permissions nothing about credentials.

    SELECT * FROM sys.credentials

    This one does not show any relations to SQL logins. 

    Any other ideas?

    Monday, November 23, 2015 11:41 AM
  • Hi,

    is any way to select SQL logins that are Mapped To Credentials as using this query I get only NULL:

    SELECT credential_id FROM sys.server_principals WHERE name = 'test_login'

    I see the list of Mapped Credentials in SQL login properties with GUI, but can not select this option. 

    Thanks

    Try below

    USE <DBNAME>
    Select db_name(),u.name,l.loginname,spr.name,spe.state_desc, spe.permission_name  from sys.syslogins l
    inner join sys.sysusers u on l.sid = u.sid
    inner join sys.server_principals spr on l.sid=spr.sid 
    inner join sys.server_permissions AS spe on spe.grantee_principal_id = spr.principal_id
    where spr.type_desc = 'SQL_LOGIN' and l.loginname = <'userloginame'>


    Thanks Saravana Kumar C

    Monday, November 23, 2015 1:40 PM
  • Don't see Mapped Credentials to any selected logins
    Monday, November 23, 2015 3:37 PM
  • Found sys table for this:

    sys.server_principal_credentials

    • Marked as answer by jori5 Tuesday, November 24, 2015 8:49 AM
    Tuesday, November 24, 2015 8:49 AM