dbo owner needed for tables with DataPerCompany=NO ?

mgm

We are using NAV 5.0 SP1 on SQL 2005.
I want to insert a record in a system table (2000000068) which has the property DataPerCompany=NO.
I also have the same issue with a custom table.
It seems that I need the DB Owner right on SQL 2005 to do this?
Of course I don't want users to have this right.
How can this be resolved?

kine

1) Security model - Extended or Standard?
2) How the permissions for the users are set up? Have they the roles per company or without specific company (Company field is empty in the Member Of table)?

mgm

1) Standard
2) User has NO restrictions on Company

garak

What for an error message pops up?

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

stryk

mgm wrote:

2) User has NO restrictions on Company

So which ROLE is assigned to the user? SUPER? Or any user-role? Does this role include the access-rights to those tables?

mgm

The table is a system table: 2000000068
The user has role SUPER
The Error Message that pops up is:

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

1088,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "ECO_50SP1W1_T.dbo.Record Link" because it does not exist or you do not have permissions.
SQL:
SET IDENTITY_INSERT "ECO_50SP1W1_T"."dbo"."Record Link ON


Another user with dbo permissions in SQL does not have any problems.
What did I forget?

kine

Answer is: Some code is trying to insert new record into the "Record Link" table but is not clearing the PK field. This is common error when using Autoincrement field. Enable debugger, find the code, clear the PK field (set it to 0) before inserting the record and you are done...

mgm

Thanx Kamil!

kine

You are welcome. Searching this forum for "SET IDENTITY_INSERT" will give you this answer quickly... ;-)

garak

To give you a background information for this errormessage ("SET IDENTITY_INSERT":

The reason is the fieldproperty AutoIncrement YES in the table "Record Link" on field "Link ID".

The AutoIncrement property is designed to always assign numbers automatically. If you want to insert a record, you must make sure that the value in the field is blank before you insert the record. This is even more critical when you are using the SQL Server Option. If you want to manually assign a value to a field that has the AutoIncrement property set to Yes, you must be member of the SQL Server db_owner database role for the database in question.

Regards