DOC : SET IDENTITY_INSERT topic has poor wording re: permissions - by AaronBertrand

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 631169 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 12/14/2010 8:39:43 AM
Access Restriction Public


The docs for SQL Server 2005 say that in order to execute SET IDENTITY_INSERT for an object, that the...

"User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles."


This is not quite true, as it implies that a user only in db_owner or only in db_ddladmin cannot call the command.  The way it is worded makes it seem like the two roles need to be tied together in order to be useful for this purpose.  How the statement should be worded is as follows:

"User must own the object, or be a member of the sysadmin fixed server role, or either the db_owner or db_ddladmin fixed database roles."

I believe the misinterpretation occurred when the statement was lifted (and re-worded) from the 2000 doc, which states:

"Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner."


Obviously the mention of db_owner and db_ddladmin here are together for convenience, but remain independent in meaning.

Different context and quite similar wording can lead to confused users (which is what spurred this entry).

Also, it is missing the fact that a regular user who has only been granted ALTER on the table in question is also able to affect the SET IDENTITY_INSERT setting.
Sign in to post a comment.
Posted by Microsoft on 12/22/2010 at 2:06 PM
Thanks Aaron. Your right. This looks like it got garbled when moved from SQL Server 2000. But actually we prefer to state the more granular permission now days. ALTER permission on the table is the key permission. Table ownership or ALTER on the schema or the role memberships all include that. I changed SQL Server 2008 R2 and Denali documentation to state that it requires ALTER permission on the table. Thanks.
Posted by AaronBertrand on 12/14/2010 at 12:06 PM
Why would someone down-vote this? You like having ambiguous and incorrect terminology in official documentation?