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
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
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.
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.
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.
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.
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.
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?
Vijayawada, it is quite easy to try for yourself with a few very simple changes to the scripts provided above…