users per database

[former_member221093]

Hello,

What is the SQL Query in order to see the list of theses users?   (with and without their roles)

See attached.

Thanks

[luisdarui]

You may use the following query:

SELECT     a.name as 'db_user', c.name as 'db_role', a.default_schema_name
FROM         sys.database_principals a
LEFT JOIN (sys.database_role_members b
                        JOIN sys.database_principals c  ON b.role_principal_id = c.principal_id) ON b.member_principal_id = a.principal_id
LEFT JOIN sys.server_principals d on a.sid = d.sid
WHERE a.type<> 'R';

You can play around with with the query removing any field in the select part of the statement.

Regards,

Luis Darui

[Matt_Fraser]

Hi Moshe-Eliashar,

You can use "sp_helpuser" for this, or "select * from sys.database_principals". Have a look at sp_helpuser (Transact-SQL).

Cheers,

Matt