Re: Set Identity_Insert On throws error

From: Dejan Sarka (
Date: 06/12/02

From: "Dejan Sarka" <>
Date: Wed, 12 Jun 2002 16:25:20 +0200


The database user has to be a member of the db_ddladmin role. And be sure to
use owner.object syntax when you refer to the table.

Dejan Sarka, SQL Server MVP
FAQ from Neil & others at:
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals -
"Ron Lounsbury" <> wrote in message
> I have a pair of tables in my database that contain current data and
> archived data from the current table.  Once a record has not been changed
> for more than 60 days, the record is moved from the current table to the
> archive table.  If an archived record is changed, it is moved back to the
> current table.  The snag in this (that we juct found) is this: the current
> table has a sequence number column that is an identity column.  In the
> archive table it is simply a number, so the transfer from current to
> isn't a problem.  We thought we had the transfer back working also, but
> discovered a problem.  All access is handled by stored procedures, and the
> UpdateRecord SP contains the following line:
>     SET IdentityInsert <currentDataTable> on
> This worked fine in development, where the developers all have DBO access
> the database, however when we get the QA guys involved, they get the
> following error on the Set Identity_insert line:
>    Server: Msg 8104, Level 16, State 1, Line 45
>    The current user is not the database or object owner of table
> 'tslNarrSessions'. Cannot perform SET operation.
> The TSQL help file states that "normal" users don't have permission to do
> SET IdentityInsert "By Default".  The "By Default" seems to indicate that
> can be granted, but I can't see how without granting far too many
> to the general users.  Can anyone tell me how I can grant permission to
> "public" to only Set IdentityInsert, or is there a better way?
> Ron Lounsbury