What permissions are required for SET IDENTITY_INSERT ON?
SQL Server 2000's SET IDENTITY_INSERT ON topic says:
Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.
While the SET IDENTITY_INSERT topic for SQL Server 2005 (and up) says:
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 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. 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 current Books Online statement is 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 about this, in the hopes that they make Books Online slightly clearer:
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