What permissions are required for SET IDENTITY_INSERT ON?
SQL Server 2000's SET IDENTITY_INSERT ON topic said:
Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.
The 2005 version said:
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 was clearly adapted from the 2000 version of Books Online, and re-written by someone who misinterpreted "db_owner and db_ddladmin" as a collective requirement, when it is not. It also missed the fact that ALTER TABLE permissions are sufficient even for a user not in any of the roles mentioned. More modern versions of the topic say, more simply:
User must own the table or have ALTER permission on the table.
In fact, I would suggest that explicitly granting ALTER is the safest way to allow a specific user to change the SET IDENTITY_INSERT settings for a table. While it does allow other types of ALTER commands, it grants far less than db_ddladmin, and obviously less than db_owner.
Anyway, let's run a quick test to prove that the 2005 Books Online statement was not accurate:
USE [master]; GO -- add a ddladmin login EXEC sys.sp_addlogin @loginame = N'test_ddladmin', @passwd = N'foo'; -- add a no-role login EXEC sys.sp_addlogin @loginame = N'test_peon', @passwd = N'foo'; GO CREATE DATABASE set_test; GO USE set_test; GO -- add a ddladmin user EXEC sys.sp_adduser @loginame = N'test_ddladmin', @name_in_db = N'test_ddladmin', @grpname = N'db_ddladmin'; -- add a no-role user EXEC sys.sp_adduser @loginame = N'test_peon', @name_in_db = N'test_peon'; GO CREATE TABLE dbo.IdentityTable(id INT IDENTITY(1,1)); GO -- give the ddladmin user explicit INSERT/SELECT: GRANT INSERT, SELECT ON dbo.IdentityTable TO test_ddladmin; GO -- give the non-ddladmin user INSERT/SELECT as well as ALTER: GRANT ALTER, INSERT, SELECT ON dbo.IdentityTable TO test_peon; GO
Now, connect to the server as either test_peon or test_ddladmin and run this code:
SET IDENTITY_INSERT dbo.IdentityTable ON; GO INSERT dbo.IdentityTable(id) SELECT 1; GO SET IDENTITY_INSERT dbo.IdentityTable OFF; GO SELECT id FROM dbo.IdentityTable; GO
Obviously this works fine, without being in both the db_ddladmin and db_owner fixed roles, and even with the correct permissions, in spite of not belonging to either role. I filed a documentation bug, and it has obviously been fixed somewhere along the way. But in case you inadvertently read some of that old documentation, hopefully this clears things up.
Don't forget to clean up:
USE [master]; GO ALTER DATABASE set_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP DATABASE set_test; GO