Hi. Attain Version 3.60GB (finsql), I have a table where the Primary Key is an Integer with the AutoIncrement property set to yes. Code inserts a record into this table (without setting a value for this field). When logged on (database authentication) with user “sa” it inserts records correctly. However when I log in with another user I get the error “The current user is not the database or object owner of table 'database.dbo.company$table. Cannot perform SET operation.” Is this an issue with the setup of the user at the MSSQL level, or a Navision issue? I can get around the problem by taking off the AutoIncrement flag and calculating the next primary key value myself, but it would be nice to use the MSSQL functionality to do this for me. Regards, Gareth.
Hi, Check the database authentication given to user in SQL Server.
Hi. I’m not sure exactly where/what I should be looking for. Under MSSQL security logins, this user has SQL Server Authentication, with a password (that CAN BE successfully updated via Navision). Nothing is ticked under Server Roles. Permit is ticked under the appropriate database in the Database Access tab. Within the database itself the tables only have Select/Insert/Update/Delete/DRI ticked for $ndo$shadow. Regards, Gareth.
The problem occurs when Navision needs to issue a SET IDENTITY_INSERT ON/OFF statement, when you are logged on as a user that is not a member of db_owner (which is why there is no problem when logging in as SA, because he always is). This statement is issued when you would like to override the autoincrement value with your own value (i.e. you supply a non-zero value for the autoinrement field in the record variable) - it is set to ON, and when you resort back to autoincrementing again - it is set to OFF. Either way, a plain user cannot execute this SET statement in SQL Server. In Navision, your users are either members of db_owner (as SA is of course), or for plain users they are members of the $ndo$shadow application role. As long as you need to use the default behaviour of an autoincrement field - that is you supply a 0 for the field value in the record variable - you will not meet this problem for any type of user. You say that you are not setting a value for this field, so I guess that is what you are trying to do. However, remember that after the INSERT, the autoincrement value that was actually used is retrieved from the server and placed into your record variable for your reference. So now it is not 0 anymore. If you do a second INSERT, you are actually now in ‘override’ mode and you will get this error. So remember, after the INSERT, to set the autoinrement field value back to 0 again. That should solve your problem. If you would like to use the override behaviour of autoincrement, you have no option than to make your user a member of db_owner for the database, in SQL Server.
Thanks Robert. Regards, Gareth.