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

SQL SERVER - Fix - Msg 230, Level 14, State 1 - The SELECT permission was denied on the column of the object , database , schema lifesaver 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)

Previous Post
SQL SERVER – User Defined Audit with SQL Server
Next Post
SQL SERVER – How to Chang Audit Location?

Related Posts

2 Comments. Leave new

  • Hey,

    I have scenario like above , we have deny for update and references SSN column particular user .

    On daily basis we getting select permission issue for this column , after doing auto fix everything was fine .

    Above update and reference deny is might be problem for that user to access it .

    Reply
  • I need to restrict an user from viewing columns in a table (other users should have full access to this table).
    TableName : dbo.TestTbl (Col1, Col2, Col3,SSN)

    So I deny access to only SSN column specified via “GRANT SELECT ON OBJECT::dbo.
    TestTbl (SSN) TO User1”
    But when I am running a query under my account “SELECT * FROM dbo.TestTbl;” i got an error “The SELECT permission was denied on the column “SSN” of the object TestTbl’, database ‘TEST’, schema ‘dbo’.” though I have SA permission. Also other users getting the same error.

    Reply

Leave a Reply

Menu