Make sure we have sql authentication on
--step 1
use MASTER
IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'testDB')
DROP DATABASE testDB;
GO
IF EXISTS (SELECT * FROM SYSLOGINS WHERE NAME = 'testlogin1')
DROP LOGIN TestLogin1;
GO
CREATE DATABASE testDB;
GO
USE testDB
GO
CREATE SCHEMA TestSchema1;
GO
CREATE TABLE TestSchema1.TestTable (id INT, col1 INT);
INSERT INTO TestSchema1.TestTable VALUES(42, 1);
--step 2
CREATE LOGIN TestLogin1 WITH PASSWORD='sqlpassword', CHECK_POLICY = OFF;
CREATE USER TestUser1 FOR LOGIN TestLogin1;
--Step 3
GRANT SELECT ON SCHEMA::TestSchema1 TO TestUser1;
GO
EXECUTE AS USER='TestUser1';
SELECT col1 FROM TestSchema1.TestTable;
REVERT;
GO
SELECT col1 FROM TestSchema1.TestTable;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
SELECT
SUSER_SNAME() AS ServerLogin, -- The login account used to connect to the SQL Server instance
CURRENT_USER AS DatabaseUser, -- The user account mapped inside the current database
ORIGINAL_LOGIN() AS OriginalLogin
--Step 4
DENY SELECT ON OBJECT::TestSchema1.TestTable TO TestUser1;
GO
EXECUTE AS USER='TestUser1';
SELECT col1 FROM TestSchema1.TestTable;
REVERT;
GO
SELECT col1 FROM TestSchema1.TestTable;
--Step 5
DENY SELECT ON OBJECT::TestSchema1.TestTable TO TestUser1;
GRANT SELECT ON OBJECT::TestSchema1.TestTable(col1) TO TestUser1;
GO
EXECUTE AS USER='TestUser1';
SELECT col1 FROM TestSchema1.TestTable;
REVERT;
GO
SELECT col1 FROM TestSchema1.TestTable;
--Step 6
--contact trainer for solution--with thanks and acknowledgement to https://sqlity.net/en/2094/column-grant-overrides-table-deny/
--housekeeping
use MASTER
IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'testDB')
DROP DATABASE testDB;
GO
IF EXISTS (SELECT * FROM SYSLOGINS WHERE NAME = 'testlogin1')
DROP LOGIN TestLogin1;
GO
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'common criteria compliance enabled', 0;
RECONFIGURE WITH OVERRIDE;
GO
--remove sql authorization if added
