:::: MENU ::::

Database user roles

When moving a database to a new instance you need to make sure all users are connected to logins, or whenever you just want to check users roles.

SELECT u.name,
CASE
WHEN (r.principal_id IS NULL) THEN 'public'
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM sys.database_principals u
LEFT JOIN (sys.database_role_members m JOIN sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN sys.server_principals l ON u.sid = l.sid
WHERE u.TYPE <> 'R'
order by u.name


So, what do you think ?