I believe the most authoritative reference you will see from Microsoft docs on the topic is this one, about the fixed database roles. The description of
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server.
db_owner fixed database role is a superset of all permissions inside the database. I like to think of it as "
sysadmin, but just for the one database."
If you want to check specifically for membership in the
db_datareader role (and ignoring membership in more powerful roles that are a superset of
db_datareader plus other permissions), you could do something like this, which lists all users that have
SELECT RoleName = r.name,
UserName = u.name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r
ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals AS u
ON rm.member_principal_id = u.principal_id
WHERE r.name = 'db_datareader';
db_datareader isn't the only way to get that permission
It's also important to note that in addition to the fixed database roles, permissions can be explicitly granted to give equivalent permissions, without even being members of a role.
For example, I can
GRANT SELECT on an entire database to a specific user. This will give similar permissions to the
db_datareader fixed database role:
GRANT SELECT ON DATABASE::[/dev/null] TO ReadOnlyUser;
You can also grant or deny permissions at more granular levels, like a specific schema:
DENY SELECT ON SCHEMA::[SuperSecret] TO ReadOnlyUser;
You can then query permissions directly to see these:
UserName = u.name,
Permission = perm.permission_name,
PermissionState = perm.state_desc,
PermissionClass = perm.class_desc,
MajorId = perm.major_id,
MinorId = perm.minor_id
FROM sys.database_principals AS u
JOIN sys.database_permissions AS perm
ON perm.grantee_principal_id = u.principal_id
WHERE u.type IN ('S','U','G');
On my database, this query gives the following results:
UserName Permission PermissionState PermissionClass MajorId MinorId
-------------- ----------- ---------------- ---------------- -------- --------
dbo CONNECT GRANT DATABASE 0 0
ReadOnlyUser CONNECT GRANT DATABASE 0 0
ReadOnlyUser SELECT GRANT DATABASE 0 0
ReadOnlyUser SELECT DENY SCHEMA 5 0
You can see how
GRANT SELECT ON DATABASE... and
DENY SELECT ON SCHEMA...are represented here.
Permissions are hard
Evaluating permission by looking at the metadata can get complicated, because there are multiple routes to get permissions: Direct grants, membership in a role, even nested membership in roles and Active Directory group membership (which can also be nested).
Depending on your use case, you might be better off looking in the functions sys.fn_my_permissions or HAS_PERMS_BY_NAME to evaluate specific permissions. I'd also recommend the article Determining Effective Database Engine Permissions on Microsoft Docs.