What permissions are required for SET IDENTITY_INSERT ON?

AaronBertrand — Tue, 14 Dec 2010 17:48:00 GMT

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:

http://connect.microsoft.com/SQLServer/feedback/details/631169

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

Search results matching tags 'Documentation', 'SET IDENTITY_INSERT', and 'Connect'

What permissions are required for SET IDENTITY_INSERT ON?