Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server GRANT/DENY on column and table level
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment