Weird behaviour during security synchronization

shei

Hi all!

A client of ours has a very weird problem with one (!) SQL login during security synchronization. They use Dynamics NAV 6.0 SP1 (Classic only) on SQL Server 2008 with a mirrored database. The synchronization process only fails with "Error 15023: User already exists in current database." if a special username is used (no special characters, it's a simple name), all other SQL logins/Windows Auths work flawless. Creating a new SQL login with same name don't work either.
The error msg itself is not the weird part, weird is, the user doesn't exist at all and NAV creates the db user and schema during synchronization as it should be but refuses to update the NAV user table (displaying the error msg instead). It works if I rename the exact same SQL Login.
I've never seen such a behaviour before and and I'm at a loss. Any ideas welcome!

kine

1) Standard or extended security model?
2) Check Member of table for the login. Sometime it makes problems when there is assigned role to the login but the login itself was deleted...
3) Open the SQL Management and check the Database-DatabaseName-Security for the login. Sometime it is a problem that the login doesn't exists in Server-Security but exists in the Database-security...

NaviDeveloper_NL

Probably there is a user with the same name in the master database. You can check it with

USE master
GO
select name from sysusers
WHERE issqluser = 1

If you add a user to the database users form, a user is created in master database. However, if you remove the user this user is not removed from master database.

Just drop the user from master database.

This can also happen:

http://blog.sqlauthority.com/2007/02/15 ... -database/

David_Singleton

or just switch to standard security model. :whistle:

NaviDeveloper_NL

David Singleton wrote:

or just switch to standard security model. :whistle:

This happens with Standard Security model. I did not test the Enhanced Security model, maybe that does not have this bug.

David_Singleton

NaviDeveloper NL wrote:

David Singleton wrote:

or just switch to standard security model. :whistle:

This happens with Standard Security model. I did not test the Enhanced Security model, maybe that does not have this bug.

So why do you need to run the sync if its standard? Is it just to create the user in the SQL database? I have just had so many problems with synchronizing that I never use it EVER, I just create users manually.

NaviDeveloper_NL

David Singleton wrote:

NaviDeveloper NL wrote:

David Singleton wrote:

or just switch to standard security model. :whistle:

This happens with Standard Security model. I did not test the Enhanced Security model, maybe that does not have this bug.

So why do you need to run the sync if its standard? Is it just to create the user in the SQL database? I have just had so many problems with synchronizing that I never use it EVER, I just create users manually.

OK my problem was a little different. The error came when I entered the database login in the table, at this point NAV does some synchronisation to SQL Server.