SQL Server GRANT/DENY on column and table level

TestColumnPrivileges.sql

-- run separate steps, one number at a time

-- 01 create database
USE master;
GO

IF DB_ID('test_security') IS NULL
	EXEC sys.sp_executesql N'CREATE DATABASE test_security;';
GO

-- 02 create test user; we don't need login for it
USE test_security;
GO

IF USER_ID('testUser') IS NULL
	EXEC sys.sp_executesql N'CREATE USER testUser WITHOUT LOGIN;';
GO

-- 03 create test table with few rows of data
IF OBJECT_ID(N'dbo.TestTable', N'U') IS NOT NULL
	EXEC sys.sp_executesql N'DROP TABLE dbo.testTable;'
GO

CREATE TABLE dbo.TestTable (
	id		TINYINT,
	code	CHAR(3),
	info	VARCHAR(10)
)
GO

INSERT INTO dbo.TestTable(
	id, code, info
)
VALUES
	(10, 'A01', 'Code A01'),
	(20, 'A02', 'Code A02'),
	(30, 'A03', 'Code A03'),
	(40, 'A04', 'Code A04'),
	(50, 'A05', 'Code A05')
;

-- 04 check if we can see the data
SELECT * FROM dbo.TestTable;

-- 05 check if testUser user can see the data
EXECUTE AS USER = 'testUser';
GO

SELECT * FROM dbo.TestTable;

-- we should see error
-- The SELECT permission was denied on the object 'TestTable', database 'test_security', schema 'dbo'.

-- 06 switch back to admin, grant SELECT to testUser and try again
REVERT

GRANT SELECT ON dbo.TestTable TO testUser;
GO

EXECUTE AS USER = 'testUser';
GO

SELECT * FROM dbo.TestTable;
-- now it works

-- 07 switch back to admin
REVERT

-- 08 REVOKE SELECT privilege from testUser, 
-- GRANT access to 'id' and 'code' columns for testUser
REVOKE SELECT ON dbo.TestTable FROM testUser;
GO

GRANT SELECT ON dbo.TestTable(id, code) TO testUser;
GO

-- 09 test
EXECUTE AS USER = 'testUser';
GO

-- that should throw the error
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'.
SELECT * FROM dbo.TestTable;

-- 10 but this should work
SELECT id, code FROM dbo.TestTable;

-- 11 switch back to admin and test DENY/GRANT behavior
REVERT

REVOKE SELECT ON dbo.TestTable(id, code) FROM testUser;
GO

DENY SELECT ON dbo.TestTable TO testUser;
GO

GRANT SELECT ON dbo.TestTable(id, code) TO testUser;
GO

-- 12 test
EXECUTE AS USER = 'testUser';
GO

-- that should throw the error
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'.
SELECT * FROM dbo.TestTable;

-- this should work
SELECT id, code FROM dbo.TestTable;

-- so: GRANT takes precedence over DENY

-- 13 switch back to admin and DENY one more time
REVERT

DENY SELECT ON dbo.TestTable TO testUser;
GO

-- 14 test again
EXECUTE AS USER = 'testUser';
GO

-- that should throw the error
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'.
SELECT * FROM dbo.TestTable;

-- this should throw the error too
SELECT id, code FROM dbo.TestTable;

-- so: looks like it's important if we do GRANT or DENY first; one more test

-- 15 switch back to admin, clean up permissions
REVERT

REVOKE SELECT ON dbo.TestTable FROM testUser;
REVOKE SELECT ON dbo.TestTable(id, code) FROM testUser;
GO

-- 16 first do GRANT, then DENY
GRANT SELECT ON dbo.TestTable(id, code) TO testUser;
DENY SELECT ON dbo.TestTable TO testUser;

-- 17 test again
EXECUTE AS USER = 'testUser';
GO

-- that should throw the error
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'.
SELECT * FROM dbo.TestTable;

-- this should throw the error too
SELECT id, code FROM dbo.TestTable;

-- 18 switch back to admin and clean up
REVERT

USE master;
GO

DROP DATABASE test_security;
GO