THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

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://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

 

Published Tuesday, December 14, 2010 1:48 PM by AaronBertrand

Comments

 

AaronBertrand said:

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

December 14, 2010 11:22 PM
 

Cameron said:

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?

January 31, 2011 10:42 PM
 

AaronBertrand said:

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.

January 31, 2011 10:48 PM
 

Cameron said:

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.

January 31, 2011 11:52 PM
 

Pablo Barral said:

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.

February 13, 2012 12:59 PM
 

RodB said:

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.

March 4, 2013 12:18 AM
 

Donna Collins said:

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.

June 26, 2015 2:45 PM
 

StanS said:

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

September 21, 2015 4:39 PM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement