December 14, 2010 | SQL Server

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:

http://web.archive.org/web/*/https://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

8 comments on this post

    • AaronBertrand - December 15, 2010, 7:22 AM

      Vijayawada, it is quite easy to try for yourself with a few very simple changes to the scripts provided above…

    • Cameron - February 1, 2011, 6:42 AM

      After spending a considerable amount of time trying to give give a custom role the ability to Identity_Insert on only one table (requiring db_admin) I found your post.
      Unfortunately when I GRANT ALTER on my table
      use MYDB
      GRANT ALTER ON dbo.MYTABLE TO MYROLE
      GO
      I receive the following error
      Msg 156, Level 15, State 1, Line 2
      Incorrect syntax near the keyword 'ON'.
      I'm running SQL Server 2000 SP4
      Any ideas?

    • AaronBertrand - February 1, 2011, 6:48 AM

      Have you tried GRANT ALL ON instead of GRANT ALTER ON?  I realize you may be giving up more permissions than you want, but GRANT ALTER wasn't introduced until 2005 IIRC.

    • Cameron - February 1, 2011, 7:52 AM

      Thanks Aaron.
      GRANT ALL ON – Successfully Completes
      But when I run my IDENTITY INSERT SP I receive the following error:
      Msg 8104, Level 16, State 1, Procedure MYSP, Line 238
      The current user is not the database or object owner of table 'dbo.MYTABLE'. Cannot perform SET operation.
      When I set the user\role to db_owner it works…
      I suspect it is the SQL Server version being 2000……
      Not sure how else to configure security except identify users who will use the specific part of application functionality and giving them db_owner access.

    • Pablo Barral - February 13, 2012, 8:59 PM

      Unfortunately, GRANT ALL does work, but it doesn't apply the GRANT ALTER…
      As AaronBertrand said: GRANT ALTER wasn't introduced until 2005 IIRC
      I guess this limits your options enormously.

    • RodB - March 4, 2013, 8:18 AM

      hi – thanks for this great post. We had exactly this issue and the GRANT option has given us a fix. Will have words to the developer (and closer code reviews) as I don't think we should be using identity_insert in production code.
      cheers.

    • Donna Collins - June 26, 2015, 9:45 PM

      I too need to modify a read/write login to be able to exec Identity_Insert. GRaNT ALL I'm afraid did not work, displaying error "The current user is not the database or object owner of table 'dbo.MYTABLE'. Cannot perform SET operation". I may have to configure the login to DDLAdmin group, which is really not what I wanted to do.

    • StanS - September 21, 2015, 11:39 PM

      It works perfectly. Great post
      /*
      If you absolutely need to do this – then creating the database role may help to keep it manageable.
      Below is the example how to accomplish it:
      Do not forget to replace [%DBNAME%]and %UserName% with Database and user name you are modifying…
      */
      — connect to database:
      USE [%DBNAME%]
      GO
      –Create new database role
      CREATE ROLE  [dbo_Schema_IdenHdl];
      GO
      –Grant Alter permission on dbo schema:
      GRANT ALTER ON SCHEMA::[dbo] TO dbo_Schema_IdenHdl;
      GO
      –Add user or user's group you need to grant "Set Indentity Insert on " to new created database schema:
      EXEC sp_addrolemember N'dbo_Schema_IdenHdl', N'%UserName%'
      GO
      use [DPTS_MDVIP]
      GO
      REVOKE ALTER ON SCHEMA::[dbo] TO [dbo_Schema_Writer] AS [dbo]
      GO

Comments are closed.