Problem with access SQL Server thru ODBC

erkasj

I'm trying to access Nav 2009 tables (SQL server) thru ODBC, but I can't find any NAV tables only SQL Server DB objects. I'm using same UserId as in the Application with Windows auth.
Before upgrading Nav2009/SQL Server we used NAV 5 with Native DB and running C/ODBC was no problem.
Can any body out there please help me as our Nav partner cannot .

-Thanks

bbrown

You need to assign permissions

rsaritzky

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.

erkasj

Hi,
Ron thanks for your reply, I was hoping your solution would solve my
problem but,
when looking at Management Studio/ User mapping tab for this userid and Database
the "Public" was checked.
Is there any more permissions to be set in SQL?
(I think our NAV Partner should be able to solve this kind of problems.)

-Thanks
Anders

bbrown

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

stryk

Maybe this could help you: http://dynamicsuser.net/blogs/stryk/archive/2010/02/16/extended-database-hardening-nav-sql.aspx

erkasj

bbrown.
Thanks for your support,
A new role created and I have ODBC running.

Jörg.
Thanks for the link reagrding NAV/SQL server Security.

How will I get the green checkmark before the Subject???

/Anders