0

SELECT IS_MEMBER("DB_DATAREADER");

The above return 1 for user who has db_datareader.

Also, it returns 1 for user who doesn't have this role but has got db_owner.

  1. I am looking for authoritative Microsoft reference about db_owner automatically having db_datareader.

  2. Is there any way to check if the db_datareader is checkmarked (assigned directly) against the user?

0

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 db_owner says:

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.

The 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 db_datareader:

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';

Membership in 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:

SELECT 
        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.

6
  • Does custom role behave the same? For example does dbowner automatically become member of custome db role?
    – variable
    Aug 20 '21 at 13:00
  • db_owner can do everything in the database. It doesn't need to be a member of other roles, because it already has all of those permissions & more.
    – AMtwo
    Aug 20 '21 at 13:04
  • Yes but SELECT IS_MEMBER("DB_DATAREADER"); returns false for db_owner
    – variable
    Aug 20 '21 at 13:12
  • 1
    @variable What problem are you trying to solve? Are you trying to evaluate effective permissions (ie, "can a user query data in this database?" or are you trying to evaluate a very specific question about role membership (ie, "is this user a member of this role, so that I can add/remove them?")
    – AMtwo
    Aug 20 '21 at 13:20
  • @variable To hopefully clarify: roles and permissions are two different concepts. db_owner is a role with all of the permissions of the database, db_datareader is a separate role that has only some of the permissions of the database. They're both two separate roles, regardless that db_datareader has access to a subset of the same permissions that db_owner has access to. The IS_MEMBER() function is just used to check if the executing user is a member of a given role, but again db_owner and db_datareader are separate roles, despite db_owner having all the same permissions & more.
    – J.D.
    Aug 20 '21 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.