NAV 5.01 SQL2005 synchronisation failed

Dynamics 365 Business Central/NAV Technical Forum
1

Hello,

we use NAV5.01 and MS SQL2005 witch ServicePack3. I can´t apply a new user in NAV cause the synchronisation between NAV and the SQL-Server failed. Any idea?

Greetings Thomas

2

welcome to DUG.

What is the error message…

3

Please excuse my english translation.

"The security system from Microsoft Dynamics NAV and the SQL Server couldn´t be successfully synchronized.

The SQL Server Login… isn´t present on the Server."

And if I am logged in as a sysadmin on the SQL Server there is an other message.

"the following sql server error occurred.

15138,“42000”,[Microsoft][ODBC SQL Server driver][SQL Server]

The database principal owns a schema-object in the database and cannot be dropped.
SQL:
{CALL[sp_revokedbaccess](?)}

I hope you understand this..
4

I was able today to solve a similar case:

Please use sql profiler on SSMS and start sync again in NAV. Profiler shows the “bad” user.

After that and still on SSMS–>VIEW–>Object explorer details---->DATABASES(select the Database where your getting the problem)---->Security---->Schemas

From here you can check which owns the schema and change accordingly, that is, removing the “bad user” and reinsert the correct owner. Thai is, if schema is ‘db_datareader’, correct owner should be “db_datareader”.

Hope it helps, regards!