Problem with access SQL Server thru ODBC

To be more specific, make sure the userid that you are using to access the NAV database has permissions to read the database in SQL. Check the userid in SQL Management Studio. I did a very similar thing - we were using an ODBC connection to our NAV50 database, and then we upgraded to NAV2009. Using the same userid, I tried to connect to our new database - but couldn't see the NAV tables. I went into SQL Management Studio, brought up the properties for the userid and checked the "user mapping" tab. The new database was not checked. I checked the new database and gave it public membership and everything was OK.

The "Public" role only grants access to the database. It does not grant permissions to any of the databases objects (tables, etc.). You must grant either the user or assigned role the permissions. There are 3 main options:

1. Grant user DB_Owner role. This is NOT preferred, as it give the user complete unhindered access to the database. This tend to be the "cheap way out" that I see many take.

2. Add the user as a member of a built in role. This may work fine if a built in role meets your needs. However many of the built in roles may grant more permissions then desired.

3. (preferred). Create a new DB role. Grant desired permissions. Make user a role member.

EXAMPLE:

Grant Select on [dbo].[Your Company$Your NAV Table] to yourrole
Go