Symptoms
When you try to insert data into a custom table, or when you run a process that inserts data into a custom table, you receive an error message that resembles the following:
The following SQL Server error(s) occurred while accessing the TableName table:
1088, "42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "NAVDBName.dbo.CompanyName$ TableName" because it does not exist or you do not have permissions.
SQL:
SET IDENTITY_INSERT "NAVDBName"."dbo"."CompanyName$ TableName" ON
Note In this example, TableName represents the customer's Microsoft Dynamics NAV table. NAVDBName represents the customer's Microsoft Dynamics NAV database name. CompanyName represents the designated company name of the customer in Microsoft Dynamics NAV.
This problem may occur in versions of Microsoft Dynamics NAV that use the Microsoft SQL Server database option.
Cause
This problem occurs if the following conditions are true:
-
In one of the fields in the table that is indicated in the error message, the AutoIncrement property is set to Yes.
-
The INSERT operation that you use explicitly populates the field that has the AutoIncrement property set to Yes.
-
You do not have one of the following permissions:
-
The SYSADMIN server role membership in SQL Server
-
The db_owner database role membership for the Microsoft Dynamics NAV database
-
The db_ddladmin database role membership for the Microsoft Dynamics NAV database
-
Resolution
To resolve this problem, use one of the following methods.
Method 1
Amend the INSERT operation so that it does not populate the field that has the AutoIncrement property set to Yes.
Method 2
Set the AutoIncrement property to No for the relevant field.
Note Both Method 1 and Method 2 may require the assistance of a Microsoft Dynamics NAV developer, especially if the amendment requires a code change. Such changes should be thoroughly tested before you apply the changes to a live Microsoft Dynamics NAV database.
Method 3
Grant Alter permissions for the custom table to the relevant application role (for example, &ndo$shadow, if you selected the Standard security model) in SQL Server Management Studio.
Note Permissions that are granted manually to tables from SQL Server Management Studio are overwritten when you run a security synchronization in Microsoft Dynamics NAV. Therefore, you must grant Alter permissions to the custom table again after you run the security synchronization.
More Information
In Microsoft Dynamics NAV, if the AutoIncrement property is set to Yes for an Integer data type field or for a BigInteger data type field, the relevant field should be automatically populated by a unique integer value that is generated by the system. The value that is used to populate the field increments consecutively for each new record that is inserted.
To implement the AutoIncrement property, Microsoft Dynamics NAV defines a SQL Server column that has the IDENTITY property associated with it. The IDENTITY property causes SQL Server to automatically populate the column as described earlier. If the IDENTITY property is turned on for a SQL Server column, users cannot explicitly populate the relevant column in an INSERT statement unless the users have one of the following permissions:
-
The SYSADMIN server role membership in SQL Server
-
The db_owner database role membership for the Microsoft Dynamics NAV database
-
The db_ddladmin database role membership for the Microsoft Dynamics NAV database
In addition to having these permissions, users must also run the following command for the INSERT operation to be completed.
SET IDENTITY_INSERT <table_name> ONWhen the error message that is mentioned in the "Symptoms" section occurs, Microsoft Dynamics NAV is trying to run this SET statement in SQL Server.
The method of explicitly populating a column that has the IDENTITY property is a method that is not typically required. The columns that have the IDENTITY property are usually populated automatically by SQL Server.
