SQL Server Home
DOC : SET IDENTITY_INSERT topic has poor wording re: permissions
12/14/2010 8:39:43 AM
User(s) can reproduce this bug
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.
SQL Server Denali CTP 1
Operating System Language
Steps to Reproduce
Go to any of the SET IDENTITY_INSERT topics:
http://msdn.microsoft.com/en-us/library/ms188059(SQL.105).aspx (2008 R2)
Inspect the statement under "Permissions"
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.
User must own the object, or be a member of the sysadmin fixed server role, or be a member of at least one of the db_owner or db_ddladmin fixed database roles, or be explicitly granted the ALTER permission on the object.
to post a comment.
Please enter a comment.
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.
on 12/14/2010 at 12:06 PM
Why would someone down-vote this? You like having ambiguous and incorrect terminology in official documentation?
to post a workaround.
Please enter a workaround.
© 2013 Microsoft