Exploring SQL Server Database User Information with a T-SQL Query

As a database administrator, understanding who has access to your SQL Server databases—and what level of access they have—is vital for ensuring data security and compliance. SQL Server offers multiple system views to help you explore this information, but often, you need to combine them effectively to get a clear picture.

In this article, we’ll break down a powerful T-SQL script that lists all database users, their mapped logins, permissions, and assigned roles—providing a single, consolidated view of user access.

The Complete T-SQL Script

SELECT
   dp.name AS DatabaseUser,
   dp.type_desc AS UserType,
   sp.name AS MappedLogin,
   perms.PermissionList,
   roles.RoleList
FROM
   sys.database_principals dp
LEFT JOIN
   sys.server_principals sp
   ON dp.sid = sp.sid
OUTER APPLY (
   SELECT
       STUFF((
           SELECT ', ' + perm.permission_name + ' (' + perm.state_desc + ')'
           FROM sys.database_permissions perm
           WHERE perm.grantee_principal_id = dp.principal_id
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS PermissionList
) perms
OUTER APPLY (
   SELECT
       STUFF((
           SELECT ', ' + rp.name
           FROM sys.database_role_members drm
           JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
           WHERE drm.member_principal_id = dp.principal_id
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS RoleList
) roles
WHERE
   dp.type NOT IN ('A', 'G', 'R', 'X')
   AND dp.name <> 'guest'
ORDER BY
   dp.name;

SELECT
   dp.name AS DatabaseUser,
   dp.type_desc AS UserType,
   sp.name AS MappedLogin,
   perms.PermissionList,
   roles.RoleList
FROM
   sys.database_principals dp
LEFT JOIN
   sys.server_principals sp
   ON dp.sid = sp.sid
OUTER APPLY (
   SELECT
       STUFF((
           SELECT ', ' + perm.permission_name + ' (' + perm.state_desc + ')'
           FROM sys.database_permissions perm
           WHERE perm.grantee_principal_id = dp.principal_id
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS PermissionList
) perms
OUTER APPLY (
   SELECT
       STUFF((
           SELECT ', ' + rp.name
           FROM sys.database_role_members drm
           JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
           WHERE drm.member_principal_id = dp.principal_id
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS RoleList
) roles
WHERE
   dp.type NOT IN ('A', 'G', 'R', 'X')
   AND dp.name <> 'guest'
ORDER BY
   dp.name;

Final Output

The query produces a report showing:

• DatabaseUser → The username inside the database.
• UserType → Type of user (SQL, Windows, External, etc.).
• MappedLogin → The corresponding server-level login.
• PermissionList → List of direct object-level permissions.
• RoleList → Roles assigned within the database.

The result gives administrators a holistic view of user access patterns.

Practical Use Cases

• Security Auditing: Identify who has excessive or direct permissions beyond their roles.
• Login Mapping Verification: Detect orphaned database users after login migrations or restores.
• Access Reviews: Generate comprehensive reports during compliance checks or internal audits.