Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics SL forum / Synchronize All Owners...
Microsoft Dynamics SL forum
Suggested answer

Synchronize All Ownership & Security Question

(0) ShareShare
ReportReport
Posted on by Tom Goddard

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

  • 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
    Blaine Bryant Profile Picture
    Blaine Bryant on at
    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.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Community Spotlight for September 2024

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,277 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,126 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans