SQL Server Permission Error

jdavis

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?

raven44

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

This error could indicate a problem with the "AutoIncrement" property of a specific field.
Does the table exist? Does it have a "AutoIncrement" field?

jdavis

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

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.

stryk

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]

jdavis

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.