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
|