Syncronization problem

Belias

yesterday i encountered a strange behavior between sql and nav. I admit i am not a sql expert, i know only the base of the base....of the base
First things first: sql 2005, nav 4sp3, sql authentication.

There is a database on server X with three users, let's call them A,B,C (creative, huh?).
i've just restored a backup from server x from witin nav to a newly created database on server Y, the original backup contained A,B,C users. I log on with A user (dbo) and i tried to synch all.
ERROR: user B does not exist in the sql database (I don't remember the exact message).
i checked user logins and i noticed that the users actually exists...I deleted and recreated user B from SSMS and then i deleted user B from within NAV.
i tried to reinsert user B and i got the same error as before while validating the field...what the?!?!
Anyway, i tried to sync nav w/o user B and i got:
ERROR: user C does not exist in the sql database (I don't remember the exact message).
Damn it! user c, too?!?!I tried to sync single login....surprise!success!!then i tried to resync all, but i got the usual error.

After this, i tried to log into nav with C user...success!...And then why nav says that this user does not exist in sql...no way!

Last chance: user A became a sysadmin, and everything went well. Then i toggled sysadmin flag from user rights...
In my opinon, the problem is now solved :whistle: :whistle: but what is the real problem?isn't this really strange?

kine

On SQL 2005 you need to have wider permissions than on SQL 2000, if you are sysadmin, you ca be sure that it will work. If you have some limited permissions, you can be in situation, that you cannot see other accounts on the SQL.

Belias

:-k ...but why synch single works?

P.S.: I forgot to say i am using enhanched security model

kine

May be the difference is that the syncing only one user is going directly to the user and sync the info. The sync all is reading "all users" and may be it needs some higher permissions to do that...

Belias

kine wrote:

May be the difference is that the syncing only one user is going directly to the user and sync the info. The sync all is reading "all users" and may be it needs some higher permissions to do that...

yes, it can be logical...another test can be use sync all with only the dbo user...this can confirm your opinion...i'm gonna try when i've some time...

Belias

i found the time

You do not have the required SQL Server permissions to perform the current security activity.

...this is the error that appears if i try to sync all if the only user in the db is the one i am currently using (which is "only" db owner).

If i try to sync only himself (with sync single) i receive no errors...if i try to insert another user, i receive

The Microsoft Business Solutions-Navision and SQL Server security systems have not been synchronized successfully.

The SQL Server login **** does not exist on the DN-NB0243 server.

=D> =D> thank you kine...i hope to find something you don't know ...one day or another (...i'm gonna get you... :whistle: :-({|= )

kine

You are welcome, and I am looking forward the day... ;-)

I just know that something similar was already described in some old post when SQL 2005 was new on the market. It means the knowledge about that comes from this site... 8)