SQl Server 2008 vs NAV 2009 vs NAV5SP1 login issue

dleroux
dleroux Member Posts: 87
edited 2010-09-28 in SQL General
Here is my situation. I have a SQL Server 2008 box with both NAV 2009 and NAV 5SP1 DBs attached.
I created a SQL (not windows) login and gave it the "public" role for both NAV DBs.
I opened each NAV DB and created the DB logins for the account there and gave it the SUPER role.
In both cases, I have login problems:
On NAV 2009, I do not have permission to read the object table
On NAV 5, I can log in, but as soon as I try to do something that involves writing to the DB, I get the error message one normally sees when logging in with a bad username/password combination - complete with "please try again".

If I add the db_owner role to the NAV 2009 DB all is well with that world

But if I add the db_owner role to the NAV 5SP1 DB I can still log in, but upon trying to write to the DB (adding a new Vendor for example), I now get a "You do not have permission to read the Vendor table". I can obviously read the table, look up records, etc. But as soon as I try to run a function that involves INSERT or MODIFY, I get the "permission to read" error.

I do have Windows login accounts on both DBs and they are working fine, though I have not tried playing with permissions on them as I do not have the ability to create new network accounts.

Can anyone tell me what I am experiencing - or perhaps provide a not-so-subtle cluebat application?

Thanks

Comments

  • Shedman
    Shedman Member Posts: 194
    At what point did you synchronise your logins?
  • dleroux
    dleroux Member Posts: 87
    I just resynched again and it has no effect. It was a good thought though.
  • bbrown
    bbrown Member Posts: 3,268
    Is SQL configured for mixed authentication?
    There are no bugs - only undocumented features.
  • dleroux
    dleroux Member Posts: 87
    Yes, SQL server is configured for mixed authentication. Both the NAV 5 and NAV 2009 databases are on the exact same database server( not just on the same machine, but both are on the default instance of SQL 2008).

    I've been operating under the assumption that I'm missing something fairly simple on the SQL Server side that is causing them to behave so differently, but maybe I should be looking at the NAV 5 side. I have not done anything to ehance the security of the DB.

    I'll post a NAV 5 specific question in that forum
  • ta5
    ta5 Member Posts: 1,164
    Everything ok with xp_ndo.dll and its related extended stored procedures?
    Regards
    Thomas
  • geronimo
    geronimo Member Posts: 90
    another option is deleting the users from nav and adding them again and after that synchronising them. this worked for me in a couple of cases.

    usually i notice it because synchronising is a lot quicker then it should be. (under 3s)
  • diptish.naskar
    diptish.naskar Member Posts: 360
    Hi,

    Let me explain this to you, the xp_ndo.dll is used to unleash the server state to the users of the database. When you have created the user in SQL Server and trying to synchronize with just the db owner to its own login if this won't suffice, as there are specific rights that should be given to the user who is synchronizing users in the NAV db, this roles include roles in the NAV db as well as in the master db, such as assigning Security admin etc.

    Hence in your case, you have to first delete the user in the NAV db, provide necessary rights of synchronization in SQL, then create the user in the NAV db and then synchronize.

    Hope this helps
    Diptish Naskar
    For any queries you can also visit my blog site: http://msnavarena.blogspot.com/

Categories