DOC : SET IDENTITY_INSERT topic has poor wording re: permissions

DOC : SET IDENTITY_INSERT topic has poor wording re: permissions by aaronbertrand

Closed

as Fixed

Type:	Bug
ID:	631169
Opened:	12/14/2010 8:39:43 AM
Access Restriction:	Public

Workaround(s)

view

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."

(http://msdn.microsoft.com/en-us/library/ms188059(SQL.90).aspx)

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."

(http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx)

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.

Details (expand)

English

SQL Server Denali CTP 1

Documentation

Not Applicable

US English

Go to any of the SET IDENTITY_INSERT topics:

http://msdn.microsoft.com/en-us/library/ms188059(SQL.90).aspx (2005)
http://msdn.microsoft.com/en-us/library/ms188059(SQL.100).aspx (2008)
http://msdn.microsoft.com/en-us/library/ms188059(SQL.105).aspx (2008 R2)
http://msdn.microsoft.com/en-us/library/ms188059(SQL.110).aspx (Denali)

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.

X64

0 attachments

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.