Problems with users on SQL 2005

Ramon

I have a client working on NAV 4.02 . They are working with SQL 2005 and are using SQL Server authentication.

This is the problem: when they try to add a new user in NAV (through Tools==>Security==Database logins), they get the followinging error message:

"The MBS- NAV Server and SQL Server security systems have not been synchronized succesfully. The SQL Server login "ARNOLD" does not exist on the SERVERNAME server"

The funny thing is: the user "arnold" does exist in SQL. Arnold can log in to NAV and have a party , without any problems. If you delete Arnold from the listy of database logins in NAV, the next user (in alphabettical order) e.g. "Betty" will raise the same error. As said before, all user can log in to NAV without any problems.

Because of this, it is not possible to add users at the moment, which is quite a problem as this company is a start up with new people...

Does anyone have an idea?

kriki

Did you install the hotfix 3 on SP2? This hotfix is for SQL2005.

kamalb

you have to create all navision users into SQL again.

and if they already exist, u have to assign roles/persmission to them insiude SQL

kine

The user creating new users and running synchronization must have enough rights on the MS SQL 2005 (db_owner, server admin or something like that). This is connected to security changes between MS SQL 2000 and 2005.

ara3n

They have to be server admin. I ran into the issue a few days ago. the user was db-owner of the database, but was still running into issues with synchronizing the users. It would error out that the user blah does not exists. after giving the person sa permission, synchronization went through.
This was on sql 2005. nav 4.0 sp3.

bbrown

The user would need to be a member of the SQL SecurityAdmin role. This role grants slightly less access than SA. The user in this role cannot create new databases and some other server level activities.

kine

ara3n wrote:

They have to be server admin. I ran into the issue a few days ago. the user was db-owner of the database, but was still running into issues with synchronizing the users. It would error out that the user blah does not exists. after giving the person sa permission, synchronization went through.
This was on sql 2005. nav 4.0 sp3.

Yes, the db_owner is not enough to create new users and see all existing users in master DB...

Teresa

Check out the difference between Standard Security and Enhanced Security. This may help you out.

darrenw80

I know this is an old thread but it helped me out. I too was all of sudden getting errors saying that users didn't exist in SQL when they really did. I tried deleting and recreating SQL users but nothing worked. This had always worked in the past. I don't know if it was a Navision update (we are currently running 4.0 SP3 Hotfix 6) that caused it or an update to SQL. We updated to SQL 2005 some time ago and I had been able to create users just fine after the update.

Anyways the problem wasn't with the other users, it was with mine. I didn't have the SQL permission necessary to read the other logins. I gave myself sysadmin permission and then everything started working again.

DenSter

Yeah sysadmin has a way of making permission errors go away