I have a user in a SQL Server 2014 database that only has permissions to insert into a table (no select permissions). When change tracking is enabled on the table, the inserts will fail with The SELECT permission was denied on the object ...
Why is this the case?
Steps to reproduce:
USE [master];
CREATE DATABASE insert_test;
GO
USE insert_test;
GO
CREATE TABLE dbo.test(id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, value NVARCHAR(50));
ALTER DATABASE insert_test SET CHANGE_TRACKING=ON;
ALTER TABLE dbo.test ENABLE CHANGE_TRACKING;
GO
CREATE LOGIN test_user WITH PASSWORD='FD3nIk1p(4$LKH!eSY';
CREATE USER test_user FOR LOGIN test_user;
GRANT INSERT ON dbo.test TO test_user;
GO
EXECUTE AS USER='test_user';
INSERT INTO dbo.test(value) VALUES(N'hello'); -- Fails with: The SELECT permission was denied on the object 'test', database 'insert_test', schema 'dbo'
/*
-- Cleanup
USE [master];
GO
DROP DATABASE insert_test;
GO
DROP LOGIN test_user;
GO
*/
Best Answer
Change tracking operations are running within the user's context, and in order to track what is being changed it has to read the row using a SELECT before it is changed.