What Takes Precedent db_datareader (GRANT) or db_denydatareader (DENY)?

If a user is a member of db_datareader, which grants access to a table, and db_denydatareader, which denies access to a table, which role will take precedent?

That’s the question someone on LinkedIn recently posted in the SQL Server Administrators group recently. Here’s a link to the question. The LinkedIn poster essentially wanted to know how SQL Server handles permissions when a user is a member of more than one database role if the roles conflict.

The short answer is: permissions in SQL Server are cumulative. If a user is a member of the db_datareader role and the db_datawriter role, the user will have both read and write permissions to the table. However, when DENY is thrown into the mix, it trumps all other assigned permissions. It doesn’t matter if multiple roles grant access to a table, if even one DENY is present for the user, the user will not have access to the table.

Here’s a link to some documentation that provides additional information and background. Database-Level Roles – SQL Server | Microsoft Docs

Testing db_datareader and db_denydatareader precedence

Sometimes an example is worth a thousand words so let’s create a simple scenario to demonstrate how db_denydatareader overrides db_datareader. For this example, I’ll use the BaseballData sample database.

Let’s start by creating two users, Arnie and Belinda.

USE MASTER; 
GO 

--create logins
CREATE LOGIN Arnie WITH PASSWORD = 'a7a68d@@#8tn'; 
CREATE LOGIN Belinda WITH PASSWORD = '**4mfn*@!96f';


--create database users 
USE BaseballData; 
GO 

CREATE USER Arnie FOR LOGIN Arnie; 
CREATE USER Belinda FOR LOGIN Belinda; 

Neither Arnie nor Belinda have access to the players table at this point.

--test access to the the players 
EXECUTE AS LOGIN = 'Arnie';

--attempt to select from the players table
SELECT TOP 5 USER_NAME(), lahmanID, nameFirst, nameLast FROM players; 

REVERT; 

Now, let’s assign Arnie to the db_datareader role. We’ll assign Belinda to the db_datareader and db_denydatareader roles.

--add Arnie to the db_datareader role
ALTER ROLE db_datareader ADD MEMBER Arnie; 
GO 

--add Belinda to the db_datareader and db_denydatareader roles
ALTER ROLE db_datareader ADD MEMBER Belinda; 
ALTER ROLE db_denydatareader ADD MEMBER Belinda; 
GO 

Test Arnie’s access to the players table.

--test access to the the players 
EXECUTE AS LOGIN = 'Arnie';

--attempt to select from the players table
SELECT TOP 5 USER_NAME(), lahmanID, nameFirst, nameLast FROM players; 

REVERT; 

Meanwhile for Belinda.

--test access to the the players 
EXECUTE AS LOGIN = 'Belinda';

--attempt to select from the players table
SELECT TOP 5 USER_NAME(), lahmanID, nameFirst, nameLast FROM players; 

REVERT; 

So Belinda, despite being a member of the db_datareader database role was denied access to the table because she was also a member of the db_denydatareader role. DENY trumps GRANT.

Finally, for good measure, let’s remove Belinda from both roles and then add them back in reverse order. This will prove that order doesn’t matter when role member is assigned.

-- remove Belinda from her roles
ALTER ROLE db_datareader DROP MEMBER Belinda; 
ALTER ROLE db_denydatareader DROP MEMBER Belinda; 
GO 

--add Belinda to the roles in reverse order
ALTER ROLE db_denydatareader ADD MEMBER Belinda; 
ALTER ROLE db_datareader ADD MEMBER Belinda; 
GO 

Running the select statement again for Belinda, produces the same result – access denied.

So, when a user is assigned to multiple database roles, the effective permissions are cumulative, except when DENY is present. DENY means deny. Period.

Testing db_datareader and db_denydatareader precedent for sysadmin and db_owner

Oh, but you say, what about members of elevated roles such as db_owner and sysadmin? Will DENY still override someone who has that level of access?

Ahh! Good question. Let’s see.

Let’s create a third user, Charlene, and make her a member of db_owner. We’ll also create a login, Dallas, and make it a member of the vaulted sysadmin server role. Note: in the example, we’ll add Dallas as database user. Members of the sysadmin role have access to database resources, such as tables, without the need for an associated database user. However, since db_denydatareader is a database role, Dallas will need a database user so we can attempt to deny the login with the database role.

USE MASTER; 
GO 

--create logins
CREATE LOGIN Charlene WITH PASSWORD = 'ynr87rn3!u*b'; 
CREATE LOGIN Dallas WITH PASSWORD = ')rbc$6t*hlq1';
GO 

--add Dallas to the sysadmin server role
ALTER SERVER ROLE sysadmin ADD MEMBER Dallas; 
GO 

--create database users 
USE BaseballData; 
GO 

--create user Charlene
CREATE USER Charlene FOR LOGIN Charlene; 
GO

--create user Dallas 
CREATE USER Dallas FOR LOGIN Dallas; 
GO

--add Charlene to the db_owner database role
ALTER ROLE db_owner ADD MEMBER Charlene; 
GO 

Now let’s test their access as members of db_owner and sysadmin to the players table.

--test access to the the players 
EXECUTE AS LOGIN = 'Charlene';

--attempt to select from the players table
SELECT TOP 5 USER_NAME(), lahmanID, nameFirst, nameLast FROM players; 

REVERT; 


--test access to the the players 
EXECUTE AS LOGIN = 'Dallas';

--attempt to select from the players table
SELECT TOP 5 USER_NAME(), lahmanID, nameFirst, nameLast FROM players; 

REVERT; 

As expected, both natively have access to the players table due to their elevated permissions.

Now, let’s add Charlene and Dallas to the db_denydatareader database role.

--add Charlene and Dallas to the db_denydatareader role
ALTER  ROLE db_denydatareader ADD MEMBER Charlene;
ALTER  ROLE db_denydatareader ADD MEMBER Dallas; 
GO 

Running the same query again as Charlene and Dallas now produces the following.

So, members of the db_owners database role can be denied access to key tables and resources within a database using database roles. However, members of the sysadmin server role will still have access even if their associated database user is denied access.

If you’ve been following along and want to clean up after your code

USE BaseballData; 
GO

DROP USER Arnie; 
DROP USER Belinda; 
DROP USER Charlene; 
DROP USER Dallas; 

USE MASTER; 
GO 

DROP LOGIN Arnie; 
DROP LOGIN Belinda; 
DROP LOGIN Charlene; 
DROP LOGIN Dallas; 

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.