SQL Server - Find sql database users and Find database roles

Script to find  all the database users and their roles

This query returns all the database users and roles attached.

The sys.database_principals returns a role for each principal in the database. The sys.database_role_members returns a row for each member in a role.

As well as giving a good overview of Database Roles  the query is useful for auditing SQL Security Policy. A common policy may be to disallow certain roles – such as db_owner to be used.

SELECT  members.name, roles.name,roles.type_desc,members.type_desc
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
    ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
    ON rolemem.member_principal_id = members.principal_id
ORDER BY members.name
  

 Related Posts on Security

SQL Server Security Policy

SQL Server – How to manage Failed Logon attempts

SQL Server - Security Risk Analysis and database security