Being a database administrator (DBA) is a rewarding experience as it allows you to have control over the server and manage user permissions. In this blog post, we will discuss the importance of GRANT and DENY in SQL Server and how they can be used to secure the environment.

Let’s consider a common requirement that often arises from application developers and business users – securing sensitive information. Imagine you have a table in your database that contains personally identifiable information, such as Social Security Numbers (SSN). Your organization mandates that no one should be able to read the SSN column, but reading other columns is allowed. Implementing such requirements can be challenging, but with the use of GRANT and DENY, it becomes much simpler.

Let’s start by creating our database and the table with the SSN field:

CREATE DATABASE PermissionsDB
GO

USE PermissionsDB
GO

CREATE USER Pinal WITHOUT LOGIN;
GO

CREATE TABLE SecureTbl (
    ID INT,
    Name VARCHAR(50),
    SSN VARCHAR(20)
)
GO

INSERT INTO SecureTbl VALUES (1, 'Pinal', '111-22-3333')
GO

In addition, we have created a user called ‘Pinal’ who needs to be denied permission to access the SSN column.

Now, let’s grant SELECT rights to everyone using this database:

-- Explicitly grant at object level
GRANT SELECT ON SecureTbl TO PUBLIC

We can also restrict the SELECT permission to specific user accounts if needed.

Next, we will explicitly deny access to the SSN column:

-- DENY at the column level
DENY SELECT (SSN) ON SecureTbl TO Pinal
GO

Now, let’s check if the user ‘Pinal’ has SELECT privileges on the table. We will change the user context using the EXECUTE AS command and try to access the table:

-- Switch the context
EXECUTE AS USER = 'Pinal'

-- The following SELECT statement will result in an error as we are selecting the SSN column
SELECT * FROM SecureTbl

-- The following SELECT statement will not result in an error as the SSN column is not in the select list
SELECT ID, Name FROM SecureTbl

-- Revert back to the original user context
REVERT

The first SELECT statement will result in an error because we have explicitly denied permission to user ‘Pinal’ from accessing the SSN field. The error message clearly indicates this:

Msg 230, Level 14, State 1, Line 21
The SELECT permission was denied on the column 'SSN' of the object 'SecureTbl', database 'PermissionsDB', schema 'dbo'.

Therefore, even though we have granted permission to read the table object, the DENY statement ensures that we cannot read the column values.

This is a simple example of how GRANT and DENY can be implemented in SQL Server to control access to sensitive information. If you have similar requirements in your environment, this technique can be useful.

Let us know if you have used this technique in your environments.