We have two Navision Databases both Navision 3.70.01 on SQL Server 2000 with Windows Login. Some of the tables are merge replicated between the two servers. Users are setup identically on both servers both in SQL and Navision and they have identical Navision Roles on the two servers. The problem is that the users can access the replicated tables from Navision on the Publisher, but access to the same tables from Navision on the subscriber is denied due to lacking SELECT Permission on the table. The users must be added to the db_owner role in SQL Server to gain access. Does anybody have an explanation and maybe a solution to this?
Now my friends. The chalenge has been solved. To make it work you must do the following on the Subscriber Database: In SQL Enterprise Manager goto the Database, Roles. In the Role Public give the necesarry permissions to the replicated tables: Select, Insert, Update, Delete. Then it works. BUT the disadvantage in this is that now anybody with access to the database through ODBC will be able to make changes to these tables because it’s no longer Navision that controls the permissions. Best Regards, Steen Fisker Navision Developer since 1990
I cant see why this should be necessary when the security systems are the same in each server and database. Did you create the subscriber database from Navision?
Yes, Robert. Both databases was created by a Navision Client. If the permissions to the replicated tables in the subscriber database are not setup in SQL Server a user that in Navision has the permissions to access the tables get an error message from SQL ODBC driver (latest driver) that he/she has no permission to SELECT, owner .dbo