What permissions required to use SET IDENTITY_INSERT statement?

[bawtry]

What permissions are required for a user to run a stored procedure which includes the SET IDENTITY_INSERT statement?

Books On Line states:
"Execute permissions [for SET IDENTITY_INSERT] default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner."

If I do not wish a user to become part of these server and database roles, and the user is not the owner of the table being updated, what priveledges do I need to grant them so they can execute a stored procedure containing the statement?

Despite advice in past threads against use of the SET IDENTITY_INSERT statement, I must use this statement to allow a set of records to be 'reinstated' to a table from another table acting much like a clipboard.

Thanks for your help
Graham Robbins
BCS

 

[osjohnm]

Hi

As SQL Server says above, you have to be a member of those database/server roles. There is no other way within SQL Server.

Your only alternative is to create a frotnend application that connects to the database. You will have to connect to the database with a sql user login and add it to one of the roles mentioned. I would suggest ddl_admin as it can do the least amount of damage. Then execute the procedure as the user you create.

That is a long way of doing it but if you can't add your users to the roles then you are out of options.

Hope this helps

John

 

[SQLSister]

I personally would never allow a user permission to SET IDENTITY_INSERT as your database design should not allow you to change the identifying field for a record ever. So it seems to me that what you have here is a design problem. Describe a bit more about the structure and why it is necessary to insert records without using the identity field as is it designed. Maybe we can think of a better way to perform the task you need to perform.