1

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
*/
1

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.

  • Stored procedures avoid the need to grant any permissions the table. – Dan Guzman Jun 29 '18 at 2:24
  • Seems a bit nasty that enabling change tracking changes the behaviour of existing query permissions. (CDC doesn't do this) – geofftnz Jun 29 '18 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.