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.