Options

Problem with access SQL Server thru ODBC

erkasjerkasj Member Posts: 4
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

Comments

  • Options
    bbrownbbrown Member Posts: 3,268
    You need to assign permissions
    There are no bugs - only undocumented features.
  • Options
    rsaritzkyrsaritzky Member Posts: 469
    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.
    Ron
  • Options
    erkasjerkasj Member Posts: 4
    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
  • Options
    bbrownbbrown Member Posts: 3,268
    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
    
    
    There are no bugs - only undocumented features.
  • Options
    strykstryk Member Posts: 645
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Options
    erkasjerkasj Member Posts: 4
    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
Sign In or Register to comment.