Change Tracking Inserts Require SELECT Permission

I’ve been writing about change tracking in SQL Server for some time now, but I recently came across a permissions issue on a change tracking table that I’d never encountered before. When attempting to insert data into a change tracking-enabled table using an account with no select permissions, I learned that the effective security principal must have both insert and select permission to write data to a table that has change tracking enabled.

Insert-only permissions for certain accounts

Before we discuss the change tracking side of this, let’s discuss first the need for insert-only permissions – that is, security principals that have permission to write to but not read from one or more tables. Although this is not an everyday need, there are some situations where a user account should be allowed to insert data into a table, but not subsequently read from that table. Some of those scenarios include:

• Logging or auditing activities
• Activity from an application where data is generated but not consumed
• IoT device activity that is generated by a bot rather than a human

In these situations, those accounts need only to insert but not select data, so granting just insert permissions is the most appropriate.

Change tracking inserts require SELECT permission

Getting back to change tracking: Part of the trigger mechanism for inserting new rows into a change tracking-enabled table requires that the effective user account have select permission, presumably to check the existing primary keys to see if the inserted data already exists. If you set a particular user account to have insert permission on said table without also granting select permission, you’ll encounter that familiar error message:

Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object ‘SalesTransactions’, database ‘OnlineSales’, schema ‘dbo’.

The simplest fix, of course, would be to grant select permissions to the account for that object. However, the principle of least privilege implies that we should only grant permissions that the account needs to perform its job. Granting select permissions would solve the problem, but it would also allow read access to an account that has no business need to consume that data.

Stored procedures to the rescue!

Fortunately, we need not abandon our commitment to the principle of least privilege. By wrapping the INSERT operation in a stored procedure and granting the user account execute rights on that sproc, we can eliminate the need to grant select permissions simply to satisfy the change tracking machinery. Even better, when we use a stored procedure and control security at that level, we can even revoke that account’s insert permission for that table (assuming the table and sproc are in the same schema, or are in different schemas with the same owner) since permissions to the underlying objects would be implicitly permitted through ownership chaining. Granting execute permission to that new insert stored procedure will solve the problem without requiring us to grant any more rights than necessary on the underlying table.

Conclusion

If you use change tracking long enough, you’ll eventually find a need to have an insert-only account on a change tracking-enabled table. Since change tracking inserts require select permission, you’ll need to adjust how you perform that insert operation for such accounts with limited rights. If changing the code is an option, I recommend using a stored procedure to encapsulate the insert operation without compromising on the level of access needed for that account.