selecting credential that are mapped to SQL login

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

Answer 1

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.

Answer 2

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

Answer 3

		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?

Answer 4

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

Answer 5

Don't see Mapped Credentials to any selected logins

Answer 6

Found sys table for this:

sys.server_principal_credentials