kriki: error: SET IDENTITY_INSERT “DATABASENAME”.”dbo”.”Record Link” ON

[Blog bot]

What does the error mean? And how to fix it?



The full error in NAV is this one:



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 “DATABASENAME.dbo.Record Link” because it does not exist or you do not have permissions.



SQL:

SET IDENTITY_INSERT “DATABASENAME”.”dbo”.”Record Link” ON





First and for all, I (actually a customer) hit the error on the “Record Link” table. But you can also hit it on other tables. Which one? All the tables with a field with property “Autoincrement”=Yes.



In SQL, this field is created as follows: [Link ID] [int] IDENTITY(1,1) NOT NULL



This means, you should never insert an explicit value in field [Link ID]. If you want to override it, you need some special permission to do it.



I found this in BOL (searching for “IDENTITY_INSERT”): User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.



We don’t want to give the normal users the sysadmin server role (I hope no one does this! They can do with your SQL server what they want [and if your SQL server is running with a domain administration account, they can do with your domain what they want]).

And neither db_owner on the database because they can do whatever they want with your database.



So I tried the db_ddladmin fixed database role, but it doesn’t seem to work! I didn’t investigate further on this one.



But “user must own the object” pointed me in the direction that seems to work.

So I checked the properties (in SQL!) of the table “Record Link” and in tab permissions, I see our $ndo$shadow role. I granted the “Alter” permission to the role and all is ok.

You can also grant the “Alter ” permission to  the public role, but I think that is too much a security leak.

At least the $ndo$shadow role is only used through the NAV client and server.





Читать дальше