SQL SERVER – Fix – Msg 230, Level 14, State 1 – The SELECT permission was denied on the column of the object , database , schema

Being a DBA is one of the most rewarding experience because you control the server as well as the way the permissions are given to each and every individual users. In my career I have often seen people try to underestimate the use of GRANT, DENY and REVOKE. Not so much for the third part, but the first two are super important and super critical.

In this blog post, let me take you through a common requirement that I hear from application developers and business users about how to secure the environment. Let us take a simple scenario to illustrate the basics of GRANT and DENY in this article.

Assume you have a table inside your database which has sensitive personally identifiable information like SSN. Your organization mandates that no one should be able to read the SSN column but if they read the other columns, it is absolutely fine. These kind of requirements are all over the place and I have seen developers struggle to implement the same.

Here is my simple shot at the scenario. Let us create our database and the table having 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


Additionally, I have created a user called ‘Pinal’ who needs to be denied permission. Now that we got our object under question, I am going to do two simple steps, a) First GRANT SELECT rights to everyone using this database. This can be restricted to the specific user account too. b) Explicitly we are going to DENY access to the SSN column.

-- Explicitly grant at object level
GRANT SELECT ON SecureTbl TO PUBLIC
-- DENY at the column level
DENY SELECT (SSN) ON SecureTbl TO Pinal
GO


The next step for us if to check if the user context of ‘Pinal’ as SELECT privileges on the table. To illustrate this, we are changing the user context using the EXECUTE AS command and trying to access the table.

-- Switch the context
EXECUTE AS USER='Pinal'
-- below would give Error as we are selecting SSN too
SELECT * FROM SecureTbl
-- No Error from below as SSN column is not in select list
SELECT ID, Name FROM SecureTbl
REVERT


In the above query, the first SELECT statement will result in the below error. This is because we have explicitly DENIED permission to user ‘Pinal’ from accessing the SSN field. The error also mentions this clearly.

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

Hence for all practical purposes, even though we have explicitly given permission to read on the Table Object, the DENY makes sure we will not be able to read the column values. This is one of the simplest way to look at GRANT and DENY implementation inside SQL Server.

Do let me know if you have used this technique inside your environments.

Reference: Pinal Dave (https://blog.sqlauthority.com)