SET IDENTITY_INSERT ON/OFF needed on application server, but ALTER permission seems dangerous. Suggestion?

Question

We are building a multi-user web app where they need an unique postId for post they create. Each post has (userId, postId) as the compound primary key.

Right now, postId is an identity value, but because of the need to support some operations that require postId to be inserted as is (no re-numbering), we decide to use SET IDENTITY_INSERT ON/OFF.

However, our DBA told us that such operation is not meant be used by the application server because the ALTER permission requirement:

Permissions

User must own the table or have ALTER permission on the table.

https://msdn.microsoft.com/en-ca/library/ms188059.aspx

If the application server got hacked, with ALTER permission it seems rather risky. Our DBA suggests us to not use identity value at all, and locally generate an unique postId per user.

Can SET IDENTITY_INSERT ON be left on globally?

If it can't be left on globally, does avoiding identity value and use local generation of postId (per user) with max(postId)+1 per user make sense? We much prefer to use identity value if possible because we are worried about possible deadlocks and performance issues associated with custom postId generation.

Answer 1

Starting with SQL Server 2012 you can use sequences like in Oracle. You may be better off with those. First, create the sequence:

CREATE SEQUENCE mySeq AS LONG START WITH 1 INCREMENT BY 1;
GO

Then have the table's primary key default to the next sequence value (instead of being an IDENTITY value):

CREATE TABLE myTable (
   myPK LONG PRIMARY KEY DEFAULT (NEXT VALUE FOR mySeq),
   myWhatever...
);

If you don't specify a PK value with an INSERT you'll get a unique, generated sequence value. It's basically the same behavior as an IDENTITY. But if you want to specify a PK value you can, as long as you don't violate the primary key's uniqueness - but again, that's the same behavior as an IDENTITY with SET IDENTITY INSERT ON.