Query to list the logins and the databases they have access

Question

Hi Friends,

I am in the need of query to list all the users with their respective databases they have access to. Can somebody please help me. Thanks,

Answer 1

I believe you'll have to run something in each database.

SELECT db_name(), perm.state_desc, usr.name
FROM sys.database_permissions perm
JOIN sys.database_principals usr on perm.grantee_principal_id = usr.principal_id
WHERE perm.class_desc = 'DATABASE'

Have a look at this, it explains the catalog views required

http://blogs.techrepublic.com.com/datacenter/?p=466