Get the permissions, roles, and default schema of database user

You can check the granted permissions, roles, and default schema of an database user by using the SSMS GUI. By the way, by using Transact-SQL, you can get these information quickly for all database users one shot.

/* List all permissions on database users & roles */
SELECT
princ.name,
princ.type_desc,
perm.[permission_name],
perm.state_desc,
perm.class_desc,
OBJECT_NAME(perm.major_id) AS [object]
FROM sys.database_principals princ
LEFT JOIN sys.database_permissions perm
ON perm.grantee_principal_id = princ.principal_id
ORDER BY princ.type_desc, princ.name, [object], perm.[permission_name]

/* List all users and associated roles */
SELECT rp.name AS database_role, mp.name AS database_user
FROM sys.database_role_members AS drm
JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id
ORDER BY database_role, database_user

/* List default schema of each user */
SELECT default_schema_name,
type_desc,
name,
create_date
FROM sys.database_principals
ORDER BY default_schema_name, type_desc, name