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.

Answer 2

It sounds like you need to evaluate your database design if this is possible. A post should be a fixed entity and an identity column as a single primary key should be sufficient. In your comment you mentioned that you might want to copy posts from one user to another user. If you want to split the post so that user1 and user2 can independently control their own versions of the post, then it's just a matter of copying all the post attributes into a new record (which creates a new identity key) and then updating the new records user attribute from User1 to User2. But if you want the users to share the same post... then you should do that with a relationship from user to post to avoid the need to maintain duplicate data in your post table. In other words, if you want to assign user1 and user2 to an identical version of the post, then create a relationship table with two fields (Post ID, User ID). This way you can simply add a user to the post by inserting a new record into the relationship table.

Example: Post 1 is owned by user 1. Post 2 is owned by user 1 and 2.

 Post Table - Key (Post ID)
 (Post ID=1, PostText="This post is important!")
 (Post ID=2, PostText="This post is also important!")

 Users - Key (User ID)
 (User ID=1, Name="Bob")
 (User ID=2, Name="George")

 Post Users - Key (Post ID, User ID)
 (Post ID=1, User ID=1)
 (Post ID=2, User ID=1)
 (Post ID=2, User ID=2)

Answer 3

This concerns me a little:

"...the need to support some operations that require postId to be inserted as is (no re-numbering)..."

I assume this sort of operation is the exception, and not the norm? I can also only assume that you're inserting the same post with the same Post ID into the same table without deleting the original? It's still not clear WHY you want to do this.

I really don't see why you'd need to worry about changing the Post ID if you're assigning posts to another user. Nothing else changes except the values in the User ID column by the sounds of it. Unless you mean you can have two or more posts with the same Post ID and the same User ID. If so, why?

To answer your questions:

1. No, IDENTITY_INSERT cannot be set globally. It is a per object, per session setting.
2. Using MAX(PostID) + 1 doesn't make sense. Is there a reason why IDENTITY(1, 1) doesn't work for the PostID column? You can re-seed if necessary.
3. Don't use application-generated UUIDs as key values. They make queries so much slower. At worst, use NEWSEQUENTIALID() in SQL Server if you absolutely want to use a UUID. UUIDs unnecessarily bloat tables and indexes, and with the exception of NEWSEQUENTIALID, are query performance killers.
4. What you could do is have a Primary key column simply called ID, and then a surrogate key called Post ID if that needs to be non-unique. That way, when you copy a post, the copy of the original gets a new ID, but still retains the original Post ID, but with no need to worry about doing anything unnecessary to the PK.

However, without any changes to the application or the DB, what I would suggest is using a stored procedure executed as the owner of the stored proc (which also owns the Posts table) to set IDENTITY_INSERT to ON only when absolutely necessary. Here is an example I've just written:

create procedure dbo.sp_CopyPost
(
    @PostID     INT
    ,@UserID    INT
)
with execute as owner
as
begin
    set nocount on;
    set identity_insert dbo.Posts on;

    begin tran
        insert into dbo.Posts
        (
            PostID
            ,UserID
            --Whatever other columns
        )
        values
        (
            @PostID
            ,@UserID
            --Whatever other values
        )
    commit tran

    set identity_insert dbo.Posts off;

    select @@IDENTITY
end

That will do what you want, based on the current wording of your question, and the comments you've made.

If you need to essentially "plug the gaps" in your identity column, you will find Microsoft-recommended queries to do so in section B here:

https://msdn.microsoft.com/en-us/library/ms186775.aspx