SQL Server Permission Error

jdavis
jdavis Member Posts: 7
edited 2007-08-01 in NAV/Navision Classic Client
Hi All-

I have recently converted a database to SQL Server and receive the following error message:

The following SQL Server error(s) occurred while accessing the XXX table:

1088,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot find the object XXX because it does not exist or you do not have permissions.

SQL:
SET IDENTITY_INSERT XXX ON.

I checked the roles for this user and she does have rights to the table in question.

Does anyone have an idea?

Answers

  • raven44
    raven44 Member Posts: 85
    Maybe this topic will help?
    http://www.mibuso.com/forum/viewtopic.php?t=18453

    Other suggestions include:
    1.) Upgrade / Install the Extended Stored Procedure
    2.) Full backup and restore of db
  • stryk
    stryk Member Posts: 645
    This error could indicate a problem with the "AutoIncrement" property of a specific field.
    Does the table exist? Does it have a "AutoIncrement" field?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • jdavis
    jdavis Member Posts: 7
    Thank you for the suggestions. I have some further information. It appears that this error occurs for certain users and not for others. I have SUPER rights on the db and do not receive this error.

    I'm guessing it is a problem with security not synchronizing properly.
  • bbrown
    bbrown Member Posts: 3,268
    SET IDENTITY_INSERT permission is granted only to members of DB_Owner role or higher (SA). The users that are working are probably members of this role.

    This error occurs when an attempt is made to insert a value into an Auto-Increment field.
    There are no bugs - only undocumented features.
  • stryk
    stryk Member Posts: 645
    This error occurs when an attempt is made to insert a value into an Auto-Increment field.
    Exactly! This problem could occur, if you do an INIT before inserting a record which has an "AutoIncrement" Primary Key field. An INIT does not initialize PK fields, thus the AI field would contain an invalid value; you have to "initialize" it explicitly with 0 (zero):
    Record.INIT;
Record.AutoIncrPKField := 0;
Record.FieldXYZ := ...;
Record.INSERT;
    [/code]
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • jdavis
    jdavis Member Posts: 7
    Thanks for the explanation. There was some custom code in Code Unit 80 that did a transfer fields from a custom table to a "posted" version of that table. The "posted" table had a field that was set to AutoIncrement. Since the value is coming from a different table it didn't need this property. I turned off AutoIncrement and users can post now. :D

Categories