Unable to synchornize navision user with SQL

Dynamics 365 Business Central/NAV User Forum
1

Hi all,

I would like to transfer user from LIVe database to test database(both using sql 2008), so i run sp_revlogin to export out users from LIVE.
test database have existing user in sql already (the user lists was updated 2010).
NOTE: I am not SQL trained… [:(]

Few questions I am not sure.
1.Do I need to delete all existing user in test database? where can I find the script to do that?
2.After which I delete do i just copy the text file exporting from sp_revlogin and execute it ?
3.Any requirement to synchronize navision after that ?
4.What other steps is needed ?

2

Have you restored SQL backup or Navision backup on Test Server of Live DB ???

3

Yes SIR,

I had restored SQL backup on Test Server a duplicate copy from Live DB .

4

Then all login of live database should be there in test DB …

5

amol,

in navision all user are there but not in SQL, that why i am unable to synchronize in navision.

6

You need to import logins from live DB of sql to Test DB

Then you need to synchronize… no need to delete it from NAV …

7

“You need to import logins from live DB of sql to Test DB”

“When i did that, system prompted user already exists…”

8

Check out this post. http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm It should help you fix orphaned users. I used to have a script to do this but this looks even easier. If it doesn’t work, let me know and I will look for the script.

9

Teresa,

Sorry for my late reply. I’ll try it out and keep posted if there’s further issues encountered.

10

Teresa,

what would be the proper procedure?

what happen now is when i lick on Form 59:Users, and did some changes, it prompted

The Micorsoft Dynamics NAV and SQL Server security systems have not been synchronized successfully.

The SQL Server login NIKEMAN77 does not exist on the KHB-NAVLAB server.

11

Hi Teresa,

After I grant db_owner to myself and re-login, i tried again to setup in user table but gotten new error.

The following SQL Server error(s) occurred:

15023,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]User, group, or role “nikeman77” already exists in the current database.

SQL:
CREATE USER [nikeman77]FOR LOGIN[nikeman77]

SOLVED: [Y]
http://www.mibuso.com/forum/viewtopic.php?t=49967

"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"