DENY overrides GRANT in SQL Server? I'm studying for exam 70-462 and I'm practicing with some exercises I find around the web. In the site "Microsoft Questions and Answers" there's one solution I don't understand.

The solution's last step is to "Deny SELECT access to the SalaryDetails table to the Employees role." However, all company employees were added to this role and because "HR Administrators are also company employees," then HR Administrators should end up without any access to the SalaryDetails table, which is not what's being requested.

Is the solution wrong, or am I missing something?

Thanks in advance for any help!
It has to do with the hierarchy of permissions and using role membership over granting permissions directly to each login. This SQLMag article shows a good image of the hierachy in SQL Server. [In the event the article ever goes dead I have copied the image below.]


You are granting DENY to the role Employees so the HR Administrators being a member of a higher level role should mean they do not directly inherit the DENY. At least that is how I understand it.

The particular section of the article referenced speaks on DENY:


  The third permission statement is DENY. A DENY statement also revokes a permission, but does it in such a way that the principal cannot inherit the permission through role membership. When you deny a permission, that principal doesn't have it, period. Denying permissions can let you get very granular with permissions. A common example is to grant everyone in a department a set of permissions through their membership in a role. But then you can fine tune the scheme by denying permissions either to certain users or to another role that only a subset of users belong to, taking advantage of the ability of users to belong to multiple roles.


I stand corrected

Let me first state I apoligize for not first testing what I was saying as I ended up proving myself wrong. I should have known better.

First lesson to learn: Test everything.

Second lesson to learn: Not all Microsoft Exam questions are perfect. I would suggest sticking with those test engines that Microsoft supports (e.g. MeasureUp.com)

Answer before the details

I will first give you what I discovered, then show you how I tested it. So my finding is that basically option D can be correct if you use REVOKE instead of DENY. This is based on the statement from Bob Beauchemin's whitepaper on SQL Server 2005 Security Best Practices:


  In SQL Server, authorization is accomplished via Data Access Language (DAL) rather than DDL or DML. In addition to the two DAL verbs, GRANT and REVOKE, mandated by the ISO-ANSI standard, SQL Server also contains a DENY DAL verb. DENY differs from REVOKE when a user is a member of more than one database principal. If a user Fred is a member of three database roles A, B, and C and roles A and B are GRANTed permission to a securable, if the permission is REVOKEd from role C, Fred still can access the securable. If the securable is DENYed to role C, Fred cannot access the securable. This makes managing SQL Server similar to managing other parts of the Windows family of operating systems.


So basically REVOKE removes the permissions and basically would be not applying any permissions to the table itself. You are not explicitly denying anyone access to the table. So if you really wanted to deny permissions you would have to remove the HRAdmins group from the Employees roles for it to work correctly.

I do not have access to Azure SQL so I could only test this on a local SQL Server 2014 instance. The script below is what I went through, included comments as I go.

USE [master];
GO
--Create a database to work with
CREATE DATABASE [Database_WorkingWithPermissions];
GO

--Create the logins (I can't create Windows Groups but should work the same way)
CREATE LOGIN HRAdmin1 WITH PASSWORD = N'Apple123';

CREATE LOGIN Employee1 WITH PASSWORD = N'Apple123';

--Create the users in the database
USE [Database_WorkingWithPermissions];
GO

CREATE USER Employee1 FROM LOGIN Employee1;
CREATE USER HRAdmin1 FROM LOGIN HRAdmin1;
GO

--Create the role
CREATE ROLE Employees

--Create the tables noted in the question
CREATE TABLE [Employees] (employeeID int IDENTITY(1,1), employeeName varchar(15));
CREATE TABLE [SalaryDetails] (employeeID int, salaryAmount decimal(12,3));
CREATE TABLE [OtherTable] (column1 int);

--Grant permissions to the roles according to requirements
GRANT SELECT ON [dbo].[Employees] TO [Employees];
GO
GRANT SELECT ON [dbo].[OtherTable] TO [Employees];
GO
DENY SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Assign membership to the role based on requirements
ALTER ROLE [Employees] ADD MEMBER [Employee1];
GO
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [HRAdmin1];
GO

--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--So HRAdmin1 does not have permissions as it should
-- So they need to be removed from the Employee role
ALTER ROLE [Employees] DROP MEMBER [HRAdmin1]
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--According to a SQL Server 2005 whitepaper Bob Beauchemin wrote on Security Best Practices
-- Revoke should be used
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
REVOKE SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;