Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL (Archived)

Synchronize All Ownership & Security Question

(0) ShareShare
ReportReport
Posted on by

Can someone tell me exactly what the "Synchronize All Ownership & Security" update does in a Dynamics SL environment with Windows authentication enabled?  We occasionally have strange problems that this update will fix, but we find we need to get everyone out to be able to run it.  Not convenient with 60+ users normally logged in and nearly 7 x 24 operations.  With more details about what this update does, we could perhaps do point fixes on individual user logins rather than everything at once.

Thanks

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Synchronize All Ownership & Security Question

    You can run this against your database(s)... and maybe store the results in a table somewhere else.

    It shows the permissions on all of your database objects and includes a re-grant script (if sometime down the road a 3rd party install or SyncOwnershipAndSecurity screwed something up...)

    Select Z.*

    FROM

    (

    select permission_name, state_desc, type_desc, U.name, OBJECT_NAME(major_id)  As ObjectName, CAST(State_desc AS varchar(100)) + ' ' + CAST(permission_name AS varchar(100)) + ' ON [' + CAST(OBJECT_NAME(major_id)AS varchar(100)) + '] TO [' + CAST(U.name collate database_Default  AS varchar(100)) + ']' As SQLScript

    from sys.database_permissions P

    JOIN sys.procedures T ON P.major_id = T.object_id

    JOIN sysusers U ON U.uid = P.grantee_principal_id

    UNION

    select permission_name, state_desc, type_desc, U.name, OBJECT_NAME(major_id) As ObjectName, CAST(State_desc AS varchar(100)) + ' ' + CAST(permission_name AS varchar(100)) + ' ON [' + CAST(OBJECT_NAME(major_id)AS varchar(100)) + '] TO [' + CAST(U.name collate database_Default  AS varchar(100)) + ']' As SQLScript

    from sys.database_permissions P

    JOIN sys.tables T ON P.major_id = T.object_id

    JOIN sysusers U ON U.uid = P.grantee_principal_id

    UNION

    select permission_name, state_desc, type_desc, U.name, OBJECT_NAME(major_id)  As ObjectName, CAST(State_desc AS varchar(100)) + ' ' + CAST(permission_name AS varchar(100)) + ' ON [' + CAST(OBJECT_NAME(major_id)AS varchar(100)) + '] TO [' + CAST(U.name collate database_Default  AS varchar(100)) + ']' As SQLScript

    from sys.database_permissions P

    JOIN sys.views T ON P.major_id = T.object_id

    JOIN sysusers U ON U.uid = P.grantee_principal_id

    UNION

    select permission_name, state_desc, 'Scalar-valued Function' AS type_desc, U.name, OBJECT_NAME(major_id)  As ObjectName, CAST(State_desc AS varchar(100)) + ' ' + CAST(permission_name AS varchar(100)) + ' ON [' + CAST(OBJECT_NAME(major_id)AS varchar(100)) + '] TO [' + CAST(U.name collate database_Default  AS varchar(100)) + ']' As SQLScript

    from sys.database_permissions P

    JOIN sys.sysobjects T ON P.major_id = T.id

    JOIN sysusers U ON U.uid = P.grantee_principal_id

    WHERE t.type = 'Fn'

    UNION

    select permission_name, state_desc, 'Table-valued Function' AS type_desc, U.name, OBJECT_NAME(major_id)  As ObjectName, CAST(State_desc AS varchar(100)) + ' ' + CAST(permission_name AS varchar(100)) + ' ON [' + CAST(OBJECT_NAME(major_id)AS varchar(100)) + '] TO [' + CAST(U.name collate database_Default  AS varchar(100)) + ']' As SQLScript

    from sys.database_permissions P

    JOIN sys.sysobjects T ON P.major_id = T.id

    JOIN sysusers U ON U.uid = P.grantee_principal_id

    WHERE t.type = 'TF'

    ) as Z

    Order by z.name, z.ObjectName

  • Suggested answer
    RE: Synchronize All Ownership & Security Question

    Tom,

    The sync process does a variety of things, some of which depend on the version of SL you are using, whether you are windows or SQL auth, whether you have BP and./or project connector, etc…

    Here is a list of most of what it does.   This list is probably not all inclusive:

    • Windows Authenticated Databases

    o Sets the DB Owner to ‘SA”

    o Creates the 07718158D19D4f5f9D23B55DBF5DF1 and E8F575915A2E4897A517779C0DD7CE users on the SQL Server only if they are missing.

    o Drops then Re-adds the E8F575915A2E4897A517779C0DD7CE user from the SL System and SL Application databases.

    o Grants rights to the E8F575915A2E4897A517779C0DD7CE user.

    o Grants rights to the 07718158D19D4f5f9D23B55DBF5DF1 user.

    o Sets the trustworthy property on the SL System and SL Application databases to TRUE.

    o Creates the MSDynamicsSL database role if it is missing and assigns it rights

    o Creates the MSDSL application role on the System and Application DBs only if they are missing

    o Assigns rights to the MSDSL role

    o Resets and Syncs various passwords

    o If BP is installed, resyncs BusinessPortal user’s rights

    o If Project Connector is installed, resyncs connectors rights

    • SQL Authenticated Databases

    o Sets the Owner of the SL System and SL Application databases to master80.

    o Creates the master80user on the server if missing.

    o Creates the CD8359B5576446f85EB67E824B4770 user if missing.

    o Drops then Re-adds the CD8359B5576446f85EB67E824B4770 user from the SL System and SL Application databases.

    o Grants rights to the CD8359B5576446f85EB67E824B4770 user.

    o Sets the trustworthy property on the SL System and SL Application databases to TRUE.

    o Resets and Syncs various passwords

    o If BP is installed, resyncs BusinessPortal user’s rights

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Synchronize All Ownership & Security Question

    Hi Tom when you have the Windows authentication and synchronize all ownership & securtiry the updates and synchronization passwords that you have on the domain and uodate the dynamics users, also updates users masters and 07718158D19D4f5f9D23B55DBF5DF1 and CD8359B5576446f85EB67E824B4770, update the servder\administrator user.

    I suggest you do not use the windows authentication mode and assign each user their account dynamics.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans