Re: Set Identity_Insert On throws error

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@reproms.si)
Date: 06/12/02


From: "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@reproms.si>
Date: Wed, 12 Jun 2002 16:25:20 +0200


Hi!

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: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"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
>
>


Relevant Pages

  • Set Identity_Insert On throws error
    ... archived data from the current table. ... the database, however when we get the QA guys involved, they get the ... Can anyone tell me how I can grant permission to ... "public" to only Set IdentityInsert, or is there a better way? ...
    (microsoft.public.sqlserver.security)
  • Security within a stored procedure
    ... I have a user with a database role of "denydatawriter" so ... data in the SQL Server database. ... there is a stored procedure that this user needs ... I have also tried to temporarily grant permission to ...
    (microsoft.public.sqlserver.security)
  • Re: User Sync.
    ... (books online). ... > I have restored the database from other server & now if i ... > try to create the user on new server & grant permission ... > it gives error, user exist in database. ...
    (microsoft.public.sqlserver.security)
  • Re: More on: Login failed for user LAPTOPASPNET
    ... You need to grant permission in the SQL Enterprise ... Manager for that user in that database. ... cut and pasted into Visual Studio it ... database requested in login 'certainteed'. ...
    (microsoft.public.vstudio.general)
  • RE: basic question on permissions..
    ... Running queries and managing the database doesn't equate to having to be a ... Perhaps all they need is DBO access to their database, not Sysadmin access ...
    (microsoft.public.sqlserver.security)