Cannot create new Windows Login on SQL - User allready exist

bastir

Dear all,

I've several problems on development databases.

If I want to create a new Windows Login in NAV I get an error message from NAV/SQL telling me, that the user or group allready exists on the server.
In fact I have checked, that this user is not existing on the server, but after the error message I find a new User and Schema in SQL-Security, but not in NAV.

I can delete schema and user, but NAV still doesn't allow me to crete the NAV Windows Login.
Maybe I should mention, that we have change our active directory a few months ago and it seems to me that the problems startet after that.
I know, that every users has a new SID now and therefore deleted all users from SQL and created new one.

Security model is set to "simple".
The detailed (and german) error message is:

Microsoft Dynamics NAV Classic
---------------------------
The following SQL Server error or errors occurred:

15023,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Der Benutzer, die Gruppe oder die Rolle 'DOMAIN\User' ist in der aktuellen Datenbank bereits vorhanden.

SQL:
CREATE USER [Domain\User] FOR LOGIN [Domain\User]

I hope that somebody had this problems before and can help me now or push me into the right direction. [-o<

Best regards
Sebastian

Ploeg

Hi Bastir,

You could check table [Windows Login] in SQL Server with Management Studio. Maybe you can find your 'hidden' user in there.

What happens when you try to login in Nav with that user?

kriki

I think that the user exists in the security of that database.
With SSMS, go to the database, then go to security of that database (not the security of the server!) and you will find the user. Delete the user and retry the whole procedure.

This happens when you have a user in SQL Server that has been added some databases. When you delete the user, the user is not removed in the databases (I am not talking about the security-tables of NAV).
To avoid the problem, you need first remove the access to databases from the user in SQL Server and then remove the user from SQL Server.

[Topic moved from 'NAV Three Tier' forum to 'SQL General' forum]

matttrax

I've also seen where the windows login is associated to the dbo user making it so you can't add them as a "normal" user.

detlef

OK, I have had this problem with 2 different customers now.
It seems to happen when a user (DOMAIN\USER) gets a new SID. Probably the user has been deleted from active directory and later re-created.
The user cannot login to NAV anymore.
If you delete the NAV windows login and add it again, you get the message that the user allready exists.
I also deleted the SQL login and database login and schema.
Has anybody found a solutions for this?

detlef

OK, solved it.
The error bastir got is not from adding the user to the NAV db but to the master db. The user would sill have been in the master db under the old SID. When you add a user in NAV it will also add it to master and this fails because the user was allready there.
So if you have a login that for some reason changes SID, here is the process:
• Open SQL Server Management Studio:
• Delete the login under security of all NAV databases and the master database
• Check the User Mapping of the user (Security.Logins, right click.properties) shouldn’t be mapped to any databases
• Delete the login under Security.Logins
• Synchronise security in NAV or add the user
The user should then be re-created in SQL using the current SID.

dadafolabi

Thanks. It worked =D>