What's the rationale for ALTER TABLE permission being required for SET IDENTITY_INSERT ON?

Question

SQL Server requires the user to have ALTER TABLE permissions in order to insert specific values into an IDENTITY column.

That seems a bit strange, given that inserts are clearly a DML operation.

To explain why this is a bit of a problem for me, remember first that databases don't roll back identity values at a transaction rollback. This is because databases allow for multiple transactions concurrently inserting.

Therefore, if a transaction that inserted a row later fails and must be repeated, one could safely re-use the identity value already retrieved from the first attempt.

I'm writing a database editor, and this approach makes the implementation of complicated inserts easier: The user is gradually building a graph of related table rows for which keys are required, but the keys are database-generated. ORMs use temporary keys for that and re-write everything after a commit, but that is obviously more difficult to implement. It's easier to just always go to the database for each new insert step, get the identity value, and roll back. Only when the user clicks save in the end, the transaction is committed, and the values should still be fine.

This works for some other databases like MySQL and Postgres, but SQL Server has the quirk of needing a surprisingly high level of privilege.

My question is why this is and whether this may be indicative of some other issue about explicitly inserting identity values which I've missed so far.

Answer 1

I don't have a source for the rationale, but certainly indiscriminate use of this facility can cause problems.

As an example (assuming int identity column with positive increment) explicitly inserting 2147483647 as the identity value will cause all subsequent inserts that rely on the identity incrementation to fail until someone manually runs a RESEED or sorts the mess out some other way.

You can use a SEQUENCE instead of IDENTITY columns to allocate the surrogate key column value. And no need to actually insert the row and roll it back in this case either - just get the next value from the sequence.