Add security role "admin" on user in Dev environment using SQL/X++

Tuesday, June 24, 2025

Add security role "admin" on user in Dev environment using SQL/X++

https://parashuramd365fo.blogspot.com/2023/07/add-security-role-admin-role-to-user-in.html

Whenever we are trying to restore DB from PROD to dev instance. After DB restore sometimes we don't have admin access in dev instance and we can't access all Dev instance module/data or we don't have super admin access.

To access all module/table/data in dev instance we required Admin role. We can assign admin role using

SQL script.

The following script would help us to add admin/any role using SQL

Execute following script to know role specific "RecId"

Select Recid , * from SecurityRole where name like 'system%'

Run following script to know what role are assigned to specific user

select * from SECURITYUSERROLE where user_ like 'paras%'

Here I want to add "System administrator" & "System user" role to user level.

Following script will help to add additional role on specific user.

insert into SECURITYUSERROLE(SECURITYROLE,USER_, ASSIGNMENTSTATUS,ASSIGNMENTMODE) values (162,'parashuram',1,1)

insert into SECURITYUSERROLE(SECURITYROLE,USER_, ASSIGNMENTSTATUS,ASSIGNMENTMODE) values (8,'parashuram',1,1)

Now user have "System administrator" and "System user" role access and he/she super admin now.

update SECURITYUSERROLE
set    SECURITYROLE = '213'              --SysAdmin
--where SECURITYROLE = '420'              --FCTG_System read only
where USER_         = 'Atul_Yadav'      -- update the name as required

select * from SECURITYUSERROLE where SECURITYROLE = '213'

select * from SECURITYUSERROLE where USER_ = 'Surya.Bh' -- update the name as required
select * from SECURITYUSERROLE where USER_ = 'admin' -- update the name as required

Select * from USERINFO Where SecurityRole
Select * from USERINFO Where SID= 'S-1-19-2762059789-1462440359-3229300925-2338188421-3114043688-2864208061-3273002221-3047239075-1510753007-4079435192'

Update UserInfo Set SID= 'S-1-19-2762059789-1462440359-3229300925-2338188421-3114043688-2864208061-3273002221-3047239075-1510753007-4079435192' where id = 'admin'
Where Id = 'Atul_Yadav'

select * from USERINFO where id = 'paul_robertson'

select * from SECURITYUSERROLE where USER_ = 'paul_robertson' -- update the name as required

select NETWORKALIAS,* from userinfo where id = 'admin' --copernicus@fctgl.onmicrosoft.com
update userinfo set NETWORKALIAS = 'atul.yadav@flightcentre.com' where id = 'admin'

select NETWORKALIAS,recid,* from userinfo where NETWORKALIAS = 'atul.yadav@flightcentre.com'
update USERINFO set NETWORKALIAS = 'disabled_atul.yadav@flightcentre.com' where recid = 5638352332

DECLARE @SID NVARCHAR(124)

DECLARE @NetworkAlias NVARCHAR(255)

--Get the SID, NetworkAlias from the original database
SELECT
@SID = [SID],
@NetworkAlias = NetworkAlias
FROM [AxDBOld].[dbo].[UserInfo]
WHERE Id = 'Admin'

--Restore the SID, NetworkAlias to the restored database
UPDATE [AxDB].[dbo].[UserInfo] SET
[SID] = @SID,
NetworkAlias = @NetworkAlias
WHERE Id = 'Admin'

SELECT
SID,NetworkAlias
FROM [AxDBOld].[dbo].[UserInfo]
WHERE Id = 'Admin'

S-1-19-1226801455-69656729-3193986079-1312974679-795089865-2864208061-3273002221-3047239075-1510753007-4079435192

UPDATE [AxDB].[dbo].[UserInfo] SET
[SID] = 'S-1-19-1226801455-69656729-3193986079-1312974679-795089865-2864208061-3273002221-3047239075-1510753007-4079435192'
WHERE Id = 'Admin'

select sid,NETWORKALIAS,* from USERINFO where id = 'Atul_Yadav'
select sid,NETWORKALIAS,* from USERINFO where id = 'Admin'

Select RecId, * from SecurityRole where name = 'system administrator'

select * from USERINFO where NETWORKALIAS = 'atul.yadav@flightcentre.com'

insert into SECURITYUSERROLE(user_,SECURITYROLE,ASSIGNMENTSTATUS,ASSIGNMENTMODE)
values('Atul',171,1,1)

insert into USERINFO(ID,NAME,NETWORKALIAS,ENABLE)
values('Atul','Atul','atul.yadav@flightcentre.com',1)

select sid,* from USERINFO where NETWORKALIAS = 'savar.sharma@flightcentre.com'

select sid,* from USERINFO where NETWORKALIAS = 'atul.yadav@flightcentre.com'