As Microsoft Dynamics SL consultants, we run into issues with connectivity to Dynamics SL, errors when opening screens or running reports. Some of these issues have straightforward causes and resolutions, but at times it is just a matter of the security getting out-of-sync in the databases. Running the “Synchronize All Ownership & Security” update scenario in Database Maintenance resolves these security issues and gets Dynamics SL running smoothly again.
What causes these inconsistencies with the security? It could be adding in a new third party application, changing the authentication method from SQL to Windows, or moving the databases between SQL servers – these are the easy ones. Every now and then something may happen in SQL Server itself to “break” the security settings. Checking the SQL Server logs may lead you to the cause but occasionally the exact cause is unknown.
Luckily there is a pretty simple, though sometimes inconvenient fix – running the Synchronize All Ownership and Security update scenario in Database Maintenance. I say inconvenient because all users must be out of Dynamics SL completely when running this process because users might have items locked that the sync process wouldn’t be able to access.
Running the Synchronize All Ownership & Security process:
On any computer that has the Dynamics SL client installed, select Start | Control Panel | Administrative Tools | Microsoft Dynamics SL Database Maintenance
Note: To run Microsoft Dynamics SL Database Maintenance, your Windows login account must be a member of the ‘sysadmin’ role in SQL Server or you must know the ‘sa’ password.
Dynamics SL databases are hosted. If your Windows account has rights, click the Connect button; otherwise, select the SQL Server Authentication option, enter the password for the ‘sa’ account, and then click the Connect button.
Once connected, you will see the status at the bottom of the screen.
Click on the Update Databases tab at the top of the screen.
To run the Synchronize All Ownership & Security process:
- Select the Synchronize All Ownership & Security in the Update Scenarios.
- Click on the Update Databases button.
Running this process can take anywhere from less than a minute to more than an hour depending on the number of databases or which authentication method you are using (Windows or SQL).
When the process is complete you will see the following message on the screen.
Click the OK button on the message and then click the Close button on the Database Maintenance (98.290.00) screen.
What is happening during this process?
The answer to that question depends on which Authentication method is used with Dynamics SL.
In a Windows Authenticated configuration:
- Sets the DB Owner to ‘SA”
- Creates the 07718158D19D4f5f9D23B55DBF5DF1 and E8F575915A2E4897A517779C0DD7CE users on the SQL Server only if they are missing
- Drops then re-adds the E8F575915A2E4897A517779C0DD7CE user from the SL System and SL Application databases
- Grants rights to the E8F575915A2E4897A517779C0DD7CE user
- Grants rights to the 07718158D19D4f5f9D23B55DBF5DF1 user
- Sets the trustworthy property on the SL System and SL Application databases to TRUE
- Creates the MSDynamicsSL database role if it is missing and assigns it rights
- Creates the MSDSL application role on the System and Application DBs only if they are missing
- Assigns rights to the MSDSL role
- Resets and syncs various passwords
- If BP is installed, resyncs BusinessPortal user’s rights
- If Project Connector is installed, resyncs connectors rights
In a SQL Authenticated configuration:
- Sets the Owner of the SL System and SL Application databases to master80
- Creates the master80user on the server if missing
- Creates the CD8359B5576446f85EB67E824B4770 user if missing
- Drops then re-adds the CD8359B5576446f85EB67E824B4770 user from the SL System and SL Application databases
- Grants rights to the CD8359B5576446f85EB67E824B4770 user
- Sets the trustworthy property on the SL System and SL Application databases to TRUE
- Resets and syncs various passwords
- If BP is installed, resyncs BusinessPortal user’s rights
This process is pretty simple and straightforward, but if you are at all apprehensive about running this process please be sure to contact your reseller first or contact the SL Support team at Boyer & Associates.
