SET IDENTITY_INSERT ON / OFF is required on the application server, but ALTER permission seems to be dangerous. Sentence?

Question

We are creating a multi-user web application where they need a unique postId for the post they create. Each post has (userId, postId) as its primary primary key.

Currently postId is an identifier value, but because of the need to support some operations that require postId inserted as is (without re-numbering), we decided to use SET IDENTITY_INSERT ON/OFF .

However, our database administrator told us that such an operation is not intended for use by the application server, since the ALTER permission requirement is:

Access rights

The user must own the table or have ALTER permission in the table.

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

If the application server is hacked, with ALTER permission, this looks pretty risky. Our database administrator suggests that we do not use the authentication value at all and locally generate a unique postId for each user.

Can SET IDENTITY_INSERT ON left globally?

If it cannot be left at the global level, avoids the identity value and uses the local generation postId (for each user) with max(postId)+1 for the user, does it make sense? We prefer to use the identity value, if possible, because we are worried about the possible deadlocks and performance issues associated with creating the PostId user generation.

Answer 1

Starting with SQL Server 2012, you can use sequences as in Oracle. You could be better. First create a sequence:

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

Then, by default, the following sequence value is specified for the primary key of the table (instead of the IDENTITY value):

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

If you do not specify a PK value with INSERT , you will get a unique generated sequence value. This is basically the same behavior as IDENTITY . But if you want to specify a PK value that you can, until you violate the uniqueness of the primary key - but again, this is the same behavior as IDENTITY with SET IDENTITY INSERT ON .

Answer 2

It looks like you need to evaluate the design of your database, if possible. The message should be a fixed entity and an identifier column, since one primary key should be sufficient. In your comment, you mentioned that you can copy messages from one user to another user. If you want to split the message so that user1 and user2 can independently control their versions of the message, then it is just a matter of copying all post attributes to a new record (which creates a new identification key), and then updating the new one will write the user attribute from User1 to User2. But if you want users to share the same message ... then you should do this with user-to-message relationships to avoid having to maintain duplicate data in your message table. In other words, if you want to assign user1 and user2 to the identical version of the message, create a relationship table with two fields (message identifier, user identifier). This way, you can simply add the user to the message by inserting a new record into the relationship table.

Example: message 1 belongs to user 1. Message 2 belongs to 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 bothers me a bit:

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

I assume that such an operation is an exception, not the norm? I can also assume that you are inserting the same post with the same mail id into the same table without deleting the original? It is still unclear WHY you want to do this.

I really don't understand why you need to worry about changing the message id if you are assigning messages to another user. Nothing else changes except the values ​​in the "User ID" column by sounds. If you do not mean that you can have two or more posts with the same mail identifier and the same user identifier. If so, why?

To answer your questions:

• No, IDENTITY_INSERT cannot be set globally. This is an object for each session.
• Using MAX (PostID) + 1 does not make sense. Is there a reason why IDENTITY (1, 1) does not work for a PostID column? If necessary, you can re-sow.
• Do not use application UUIDs as key values. They make queries much slower. In the worst case, use NEWSEQUENTIALID () in SQL Server if you absolutely want to use the UUID. UUIDs over-inflate tables and indexes, with the exception of NEWSEQUENTIALID, which are query performance killers.
• What you could do is have a primary key column, simply called an identifier, and then a surrogate key called Post ID, if that should not be unique. Thus, when copying a message, a copy of the original receives a new identifier, but at the same time retains the original identifier of the message, but there is no need to worry about doing something unnecessary for the PC.

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

 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 

This will do what you want based on the current wording of your question and the comments you made.

If you need to substantially “plug the gaps” in the identification column, you will find the queries recommended by Microsoft in section B here:

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