Re: Set Identity_Insert On throws error
From: Anith Sen (anith@bizdatasolutions.com)
Date: 06/12/02
- Next message: Ron Lounsbury: "Re: Set Identity_Insert On throws error"
- Previous message: Dejan Sarka: "Re: Set Identity_Insert On throws error"
- In reply to: Ron Lounsbury: "Set Identity_Insert On throws error"
- Next in thread: Ron Lounsbury: "Re: Set Identity_Insert On throws error"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Anith Sen" <anith@bizdatasolutions.com> Date: Wed, 12 Jun 2002 09:23:50 -0500
SET IDENTITY_INSERT execute permissions default to the sysadmin
fixed server role, and the db_owner and db_ddladmin fixed database
roles, and the object owner.
There is no way to grant previleges to SET IDENTITY_INSERT.
- Anith
"Ron Lounsbury" <rlounsbury@123Progeny.net> wrote in message
news:u94gJrhECHA.2432@tkmsftngp02...
> 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
archive
> isn't a problem. We thought we had the transfer back working also, but
just
> 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
to
> 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
a
> SET IdentityInsert "By Default". The "By Default" seems to indicate that
it
> can be granted, but I can't see how without granting far too many
priveleges
> 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?
>
> TIA
> Ron Lounsbury
>
>
- Next message: Ron Lounsbury: "Re: Set Identity_Insert On throws error"
- Previous message: Dejan Sarka: "Re: Set Identity_Insert On throws error"
- In reply to: Ron Lounsbury: "Set Identity_Insert On throws error"
- Next in thread: Ron Lounsbury: "Re: Set Identity_Insert On throws error"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
