SQl Server 2008 vs NAV 2009 vs NAV5SP1 login issue

dleroux

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

Shedman

At what point did you synchronise your logins?

dleroux

I just resynched again and it has no effect. It was a good thought though.

bbrown

Is SQL configured for mixed authentication?

dleroux

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

Everything ok with xp_ndo.dll and its related extended stored procedures?
Regards
Thomas

geronimo

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

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