Grant UPDATE and SELECT on specific columns in a table – SQL Server

This post briefs how we can Grant UPDATE and SELECT permissions to specific columns of a table in SQL Server without using a view. So that, this partial vertical access control strategy can help us to manage the permissions directly at the table level. It is always good to set the access permissions at the table level especially if we have some sensitive data in the tables. For example, a user has the SELECT permission on a table and uses the * operator instead of the column names explicitly in the SELECT query, he will get all the columns in the output including the columns that store the sensitive data also. So, to avoid this, we can explicitly DENY or GRANT the access permission on these columns directly in the table. This helps us avoid the exposure of sensitive data unintentionally.

In order to demonstrate how this selective column access permission helps us in adding an extra layer of security on the table data, let’s create the sample table with some dummy data and set the appropriate permission on the table.

USE [TestDB]
GO

DROP TABLE IF EXISTS dbo.[tbl_EmpMaster]
GO

--Create a Employee master table with some sample columns
CREATE TABLE [dbo].[tbl_EmpMaster](
	[BusinessID] [int] NOT NULL,
	[NationalIDNumber] [nvarchar](15) NOT NULL,
	[LoginID] [nvarchar](256) NOT NULL,
	[JobTitle] [nvarchar](50) NOT NULL,
	[BirthDate] [date] NOT NULL,
	[MaritalStatus] [nchar](1) NOT NULL,
	[HireDate] [date] NOT NULL,
	[Gender] [nchar](1) NOT NULL,
	[SalesRegion] varchar(25) NOT NULL,
	[Salary] [decimal](18, 2) NULL
)
GO

--Insert sample data into table
INSERT [dbo].[tbl_EmpMaster]
([BusinessID], [NationalIDNumber], [LoginID], [JobTitle], [BirthDate], [MaritalStatus], [HireDate], [Gender], [SalesRegion], [Salary])
VALUES
(1, N'295847284', N'adventure-works\ken0', N'Sales Lead', CAST(N'1969-01-29' AS Date), N'S', CAST(N'2009-01-14' AS Date), N'M', 'Region-1', CAST(20000.00 AS Decimal(18, 2))),
(2, N'245797967', N'adventure-works\terri0', N'Sales Executive', CAST(N'1971-08-01' AS Date), N'S', CAST(N'2008-01-31' AS Date), N'F', 'Region-2', CAST(15000.00 AS Decimal(18, 2)))
GO

--Get the data from the table
SELECT * FROM dbo.tbl_EmpMaster
GO

This is the sample table with dummy data.

Next, let’s create a SalesUser in TestDB Database with an SQL Login named as SalesUser.

USE master
GO
--Create Sales user
CREATE LOGIN SalesUser WITH PASSWORD = 'PasswordTest'
GO

USE [TestDB]
GO
--Create Sales User into TestDB with SalesUser Login
CREATE USER SalesUser FOR LOGIN SalesUser
GO

Now, we have the below requirement:

1. SalesUser should be able to perform the update operation only on the SalesRegion column in the table tbl_EmpMaster, and he should not be able to update any other column in the table.
2. SalesUser should not be able to perform the select operation on the Salary column but he should be able to read other columns.

How to GRANT UPDATE permission on specific columns in a table

In order to GRANT the UPDATE permission on a few columns of the tbl_EmpMaster table, we can use the below SQL command:

GRANT UPDATE ON dbo.tbl_EmpMaster(SalesRegion) TO SalesUser

To test it, we can log in with the SalesUser credentials into a new query window and try to execute an update command on the SalesRegion column.

UPDATE dbo.tbl_EmpMaster
SET SalesRegion = 'Region-1'
WHERE [BusinessID] = 2

Now, let’s try to update JobTitle along with the SalesRegion column. It will throw the below error:

UPDATE dbo.tbl_EmpMaster
SET SalesRegion = 'Region-1', JobTitle = 'Sales Lead'
WHERE [BusinessID] = 2

Similarly, we can DENY the UPDATE permission on certain columns of a table for specific users or groups if needed.

How to GRANT SELECT permission on specific columns in a table

In order to GRANT the SELECT permission on a few columns of the tbl_EmpMaster table, we can use the below SQL command:

GRANT SELECT ON dbo.tbl_EmpMaster
(BusinessID,NationalIDNumber,LoginID,JobTitle,BirthDate,MaritalStatus,HireDate,Gender,SalesRegion) 
TO SalesUser

In order to test it, we can log in with the SalesUser credentials into a new query window. Then we can execute a select query on the tbl_EmpMaster table. Note that we are explicitly using the column list in the select query on which SalesUser has the SELECT permission.

SELECT
BusinessID,NationalIDNumber,LoginID,JobTitle,BirthDate,MaritalStatus,HireDate,Gender,SalesRegion
FROM dbo.tbl_EmpMaster
GO

Now, let’s try to use the * operator instead of the column names to fetch all the columns of the table tbl_EmpMaster. Therefore, it will throw the below error:

SELECT *
FROM dbo.tbl_EmpMaster
GO

Similarly, we can DENY the SELECT permission on certain columns of a table for specific users or groups if needed.

Thanks for the reading. Please share your inputs in the comment section.