sql query to assign system admin and system user role to all users In d365 finance in ssms

Karun D 1 Reputation point
2023-01-05T07:33:42.193+00:00

Hi Team,
I need to write a sql query in Ssms to assign system admin and system user security role to all the users. Please help

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

1 answer

Sort by: Most helpful
  1. Karun D 1 Reputation point
    2023-01-05T07:45:33.35+00:00

    For example -
    How can I write below x+ + code in sql query format in MS sql server management studio

    static void SR_AssignRoleToAllUsers(Args _args)

    {
    SecurityRole role;
    SecurityUserRole userRole;
    boolean added;
    Advertisements

    REPORT THIS AD

    UserInfo            userInfo;  
    ;  
     
    select role where role.Name == ‘Budget clerk’;  
    while select userInfo  
    {  
        select * from userRole  
            where userRole.SecurityRole == role.RecId &&  
                userRole.User == userInfo.id;  
     
        if (!userRole || (userRole.AssignmentStatus != RoleAssignmentStatus::Enabled))  
        {  
            info(strFmt(‘Role %1 added to the user %2 successfully.’, role.Name, userInfo.id));  
     
            userRole.User = userInfo.id;  
            userRole.SecurityRole = role.RecId;  
            userRole.AssignmentMode = RoleAssignmentMode::Manual;  
            userRole.AssignmentStatus = RoleAssignmentStatus::Enabled;  
            SecuritySegregationOfDuties::assignUserToRole(userRole, null);  
        }  
        else  
        {  
            warning(strFmt(‘skipping – Role %1 to the user %2.’, role.Name, userInfo.id));  
        }  
    }  
    

    }

    Was this answer helpful?