We have NAV 2009 Classic Client installed in a Citrix environment. We created a new NAV database on a Windows Server 2008/SQL Server 2008 server. We set our collation to SQL Collation “Western-European dictionary sort order, code page 1252, case-insensitive, accent-sensitive (52)” which equates to “SQL_Latin1_General_CP1_CI_AS” in SQL Server terminology. Using the NAV backup/restore, we restored a company from a database that is set to Windows collation, Latin1_General_CI_AS. We verified that all columns in the new database are set to SQL_Latin1_General_CP1_CI_AS.
When NAV users that have sysadmin rights on the server open the database, everything works fine. When users who don’t have sysadmin rights open the database they receive the following error.
The ? collation is not supported by Microsoft Dynamics NAV Classic
We created a new database on a Windows Server 2003 R2/SQL Server 2005 server with the same collation. The issue is not reproduced in this environment.
We also installed NAV 2009 on a workstation to eliminate the Citrix variable, but the issue still exists, so it seems to be related somehow to the Windows Server 2008/SQL Server 2008 server.
If anyone has any ideas about what may be causing this, we’d like to hear from you.
I wanted to change the name of this post to “Non-Sysadmin Users Cannot OPen NAV 2009 Database”, but can’t. This issue apeared when we tried to migrate our NAV environment to Windows Server 2008/SQL Server 2008. It turns out that all NAV Windows logins that do not have sysadmin rights on the database server require View Any Definition rights. That is the only solution that we found to this issue.
Seemingly MS has in SQL2008 “solved” problem with two in parallel used collation systems - “SQL” and “Windows”, and your issue was somehow connected with new approach, where “SQL” collations in fact no more exist…
These now at last eliminated collations were stretched along from SQL 7.5 and pre-unicode times for backward compatibility and always happened to cause conflicts sometimes, or some weird behaviour as in your case.
Actually this didn’t have anything to do with collation. This issue apeared when we tried to migrate our NAV environment to Windows Server 2008/SQL Server 2008. It turns out that all NAV Windows logins that do not have sysadmin rights on the database server require View Any Definition rights. That is the only solution that we found to this issue. I wanted to change the name of this post to “Non-Sysadmin Users Cannot Open NAV 2009 Database”, but couldn’t. Thanks.
then I will rather move this thread to SQL forum for our SQL guru Jorg to see - nevertheless solution you describe worked for you, it sounds rather strange, because high-end rights as dbowner, etc, are actually NOT needed, and shouldn’t be.
What you have different, is upgrading from older SQL, but your’e not the only one again - there should be some different reason, and your solution actually happens to be a workaround.
Need for giving top-level rights in SQL already have been mentioned as remedy in different context, but it is dangerus solution anyway…
This wasn’t really an upgrade. We’re in the middle of an implementation and we recently deployed our new hardware for the production environment. Trace flag 4616 is supposed to give the application role VIEW ANY DEFINITION rights that are required. It does seem strange that this becomes an issue with SQL Server 2008. Thanks for moving this to the correct forum.
Issue with TF4616 was really funny - without it, Navision’s current users count system “saw” always only ONE user, so actually unlimited count of concurrent users were able to log in No need to by additional licenses for users…
MS solved it in very ‘microsoftish’ way - finsql.exe checked for TF4616 on startup, and if not set, quit with correponding error message.
VIEW ANY DEFINITION implies VIEW ANY DATABASE, too (according to http://msdn.microsoft.com/en-us/library/ms186717.aspx ) but VIEW ANY DATABASE is a “weaker” permission and should be sufficient to solve the problem.
No need to by additional licenses for users…