SQL 2005 Navision Database and Single User Synchronization

Dynamics 365 Business Central/NAV Technical Forum
#1

Good afternoon, folks!

We’re testing an upgrade of our Navision Attain database from 3.10 (Native) To Navision/MS-Dynamics running on SQL Server 2005, and I’ve encountered an issue with the single user synchronization that has me flummoxed. (I’ll add here that I have limited experience with SQL Server. =P )

The issue started after we imported our DB into the test DB we created on SQL Server. I created the SQL Logins for the database users on the SQL Server, then assigned them to the database. I then logged into the Dynamics client, opened the ‘Database Logins’ page, checked the assigned roles, and then tried to synchronize a single user logon, and got the following errors:

"Microsoft Dynamics NAV

229, “42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object ‘$ndo$usrproperty’, database ‘TestNov08’, schema ‘dbo’.

229, “42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]The DELETE permission was denied on the object ‘$ndo$usrproperty’, database ‘TestNov08’, schema ‘dbo’."

When I checked the database tables, there were two with similar names: ‘$ndo$usrproperty’, and ‘$ndo$dbproperty’. The $dbproperty table had ‘Public - database role’ SELECT permissions, but the $usrproperty table had no permissions on it at all. As a test, I added the Public-Select permission to the $usrproperty table, and half of the error disappeared when I tried the single-user synch. So I added Public-DELETE to the $usrproperty table as well, and then tried the single user synch again.

This time I got a whole new error:

"Microsoft Dynamics NAV

15151, “42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object ‘company_name_$21$2’ because it does not exist or you do not have permission.

SQL: GRANT SELECT, INSERT, UPDATE, DELETE on ‘company_name_$21$2’ TO [$ndo$arD8A55E46425A314E98CB36BEBF4B1659]

I’ve been working on our test environment long enough now to know that it’s asking me to grant those SQL permissions to one of the ‘Application Role’ users on the SQL/Navision system, but there’s one problem: the table ‘company_name_$21$2’ it’s asking for doesn’t exist. I can’t find it anywhere, and I’m at my wits’ end trying to figure this out.

Any and all assistance gratefully appreciated. [ :slight_smile: ]

Regards,

Bert Van Vliet

#2

Hi Bert,

Are you still on 3.10?

If so, that could be at least part of your problem.
I think you should consider an upgrade to at least 4.0 SP3 when using SQL2005.

#3

Hi Alexander!

Sorry, I guess I didn’t state that part clearly enough - we’re currently on Navision Attain 3.1 (Native). Part of the migration with SQL Server 2005 involves upgrading to the new Navision Dynamics (5.0?). Our Database is a DB created in SQL 2005 through the Dynamics client, with the data imported from a backup of our previous DB. So we’re on 5.0, technically. :slight_smile:

Thanks and regards,

Bert V.V.

#4

Are you a member of db_owner in this database?

#5

Hi Dean!

My user ID for the system is set up to have ‘sysadmin’ permissions, and is also a member of ‘public’ under the Server Roles.

The Default Schema being assigned to all new users when we set the test database as their default database is ‘dbo’, and they all have ‘public’ group membership. When we try to synchronize their Navision logons from the client with the SQL database that things get wonky.

Regards,

Bert V.V.

#6

When you create the database from wthin the 5.0 exe’s, and then do a NAV restore, you should not have any of these problems, assuming that you ran the migrate.fob process to eliminate null dates from the database. I would probably try to explicitly assign db_owner to the user, just to make sure this ‘implied’ permission is not the cause of your issue. SQL Server is very protective of certain privileges, and assigning dbo privileges to public feels like something of a ‘workaround’.