title: SET IDENTITY_INSERT ON/OFF needed on application server, but ALTER permission seems dangerous. Suggestion?
body: <p>We are building a multi-user web app where they need an unique <code>postId</code> for <code>post</code> they create.  Each <code>post</code> has <code>(userId, postId)</code> as the compound primary key.</p>
  
  <p>Right now, <code>postId</code> is an identity value, but because of the need to support some operations that require <code>postId</code> to be inserted as is (no re-numbering), we decide to use <code>SET IDENTITY_INSERT ON/OFF</code>.</p>
  
  <p>However, our DBA told us that such operation is not meant be used by the application server because the ALTER permission requirement:</p>
  
  <blockquote>
    <p><strong>Permissions</strong></p>
    
    <p>User must own the table or have ALTER permission on the table.</p>
  </blockquote>
  
  <p><a href="https://msdn.microsoft.com/en-ca/library/ms188059.aspx" rel="nofollow">https://msdn.microsoft.com/en-ca/library/ms188059.aspx</a></p>
  
  <p>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 <code>postId</code> per user.</p>
  
  <p>Can <code>SET IDENTITY_INSERT ON</code> be left on globally?</p>
  
  <p>If it can't be left on globally, does avoiding identity value and use local generation of <code>postId</code> (per user) with <code>max(postId)+1</code> 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.</p>