Finance | Project Operations, Human Resources, ...

Sql query to assign system admin and system user role to all users

(0) Share

Report

Posted on by Karuna D

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

thanks

I have the same question (0)

All responses (9)

Answers (0)

Deepak Agarwal 8,602 on at

Like (0)

Report

Hi Karuna,

May I know why you want to give system admin access to all users in your system?

Was this reply helpful? Yes No
Karuna D 15 on at

Like (0)

Report

Actually, that’s for test environment where all user need to have sysadmin and to avoid manual work after refresh, I am writing a sql script that’s why.

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 301,231 Super User 2025 Season 2 on at

Like (0)

Report

Hi Karuna,

Can you tell why all users would need the system administrator role in a test environment? If it is a copy of the production database, they will have access to sensitive data. If they need to test functionality, preferably, they need to test it with the appropriate roles assigned to prevent unexpected issues when moving changes to the production environment.

I'm not a SQL expert. You would need the tables UserInfo (loop all users), SecurityRole (find record ID for the SysAdmin role) and SecurityUserRole (Insert or update the SysAdmin role assignment).

Instead of using an SQL script, have you considered a Power Automate flow?

Was this reply helpful? Yes No
Suggested answer

Deepak Agarwal 8,602 on at

Like (1)

Report

I dont think giving Admin access to all used in test is a wise thing to do. You can always export user roles from one system and import them in another system or may be import after DB restore.

Was this reply helpful? Yes No
Karuna D 15 on at

Like (0)

Report

Can you please help me with the sql query to loop through all users and assign SA in SSMS?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,231 Super User 2025 Season 2 on at

Like (0)

Report

Hi Karuna,

If you need specific help on a SQL script, you might ask a question on a Microsoft SQL forum. learn.microsoft.com/.../sql-server. Volunteers on that forum might know how to use SQL statements better than an average Dynamics professional.

Was this reply helpful? Yes No
JamieNicholson84 35 on at

Like (0)

Report

Hi Karuna,

If this is D365 FinOps, all users automatically get the 'System User' role automatically when created. (Atleast it does in ours)

As previously mentioned, it is very bad practice to give everyone access as System Access even in a Test environment. It is best to leave their access the same as what it is in Production. Apart from a few manual exceptions to the case.

What we also do is we created an empty (no permissions) user role called "EnabledForTesting" and we assign that role in Production to users that we want enabled in the Test environment after we do a refresh. If you want everyone enabled (Probably not good practice either) then you could just enable all users with the "UPDATE [USERINFO] SET [ENABLE] = 1" SQL Commend (This doesn't assign new roles to them).

We only want to enable System Admins, and users that have the role "EnabledForTesting". So we run the below SQL after each refresh.

UPDATE [USERINFO] SET [ENABLE] = 1

WHERE [ID] IN (SELECT [USER_] FROM [SECURITYUSERROLE]

WHERE [SECURITYROLE] IN (SELECT [RECID] FROM [SECURITYROLE]

WHERE [SECURITYROLE].[AOTNAME] IN ( '-SYSADMIN-', 'EnabledForTestingRole' )));

One day we will move this into a custom "Post refresh" runnable x++ class with all our other post refresh sql tasks that we do.

I know this might not answer your direct question, but hopefully it helps thing of alternative processes that might assist with what you are achieving.

Was this reply helpful? Yes No
Karuna D 15 on at

Like (0)

Report

Hi Jamie,

Thanks for responding, I have one issue here. If I want to assign the SA to entire user list then in that case I will be required to insert all users in systemuserrole table as it gets empty after a refresh. Then how can I insert the user from user info table to systemuserrole table and then assign SA to those users. I am not able to loop through in ssms

Was this reply helpful? Yes No
Karuna D 15 on at

Like (0)

Report

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 reply helpful? Yes No

Join the conversation

Community site session details

Sql query to assign system admin and system user role to all users

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Sql query to assign system admin and system user role to all users

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates