Adding users to D365FO with SQL

You can use d365fo.tools to add users to an environment after a database refresh.

Another quick way to achieve the same is with the following SQL:

CREATE PROCEDURE #AddUserFromExisting 
	@existingUserId varchar(100), 
	@newUserId varchar(100), 
	@newName varchar(100), 
	@newNetworkAlias varchar(100),
	@externalId varchar(100),
	@newSID varchar(200)
AS
BEGIN
	IF (SELECT COUNT(*) FROM USERINFO WHERE Id IN (@existingUserId, @newUserId)) = 1
	BEGIN
		INSERT INTO USERINFO(Id, Name, SID, NETWORKALIAS, EXTERNALID, ENABLE, COMPANY, NETWORKDOMAIN, ENABLEDONCE, LANGUAGE, HELPLANGUAGE, PREFERREDTIMEZONE, ACCOUNTTYPE, DEFAULTPARTITION, IDENTITYPROVIDER, EXTERNALIDTYPE, PREFERREDLOCALE, INTERACTIVELOGON)
		SELECT @newUserId, @newName, @newSID, @newNetworkAlias, @ExternalId, 1, COMPANY, NETWORKDOMAIN, ENABLEDONCE, LANGUAGE, HELPLANGUAGE, PREFERREDTIMEZONE, ACCOUNTTYPE, DEFAULTPARTITION, IDENTITYPROVIDER, EXTERNALIDTYPE, PREFERREDLOCALE, INTERACTIVELOGON
		from USERINFO
		WHERE ID = @existingUserId

		INSERT INTO SECURITYUSERROLE (USER_, SECURITYROLE, ASSIGNMENTSTATUS, ASSIGNMENTMODE)
		SELECT @newUserId, SECURITYROLE, ASSIGNMENTSTATUS, ASSIGNMENTMODE
		FROM SECURITYUSERROLE
		WHERE USER_ = @existingUserId

		INSERT INTO SYSUSERINFO (ID, EMAIL, EVENTPOLLFREQUENCY, DOCUHANDLINGACTIVE, EVENTWORKFLOWSHOWPOPUP, EVENTWORKFLOWTASKSINCLIENT, HELPTHEME, DENSITY, THEME, STARTPAGE)
		SELECT @newUserId, EMAIL, EVENTPOLLFREQUENCY, DOCUHANDLINGACTIVE, EVENTWORKFLOWSHOWPOPUP, EVENTWORKFLOWTASKSINCLIENT, HELPTHEME, DENSITY, THEME, STARTPAGE
		FROM SYSUSERINFO
		WHERE Id = @existingUserId

		PRINT 'User added!'
	END
	ELSE
	BEGIN
		PRINT 'User not added - either the @existingUserId user does not exist, or the @newUserId already exists!!'
	END
END
GO

You can then execute this against your environment database with:

exec #AddUserFromExisting 'Paul.Noakes', 'New.Person', 'New Person', 'New.Person@waywo.co.uk', 
'2003202347D43259', S-1-19-1231215433-2034443400-426345887-2298881171-3297775036-2864255561-3244402221-3055539075-1510755507-4073335192'

You can find the above values for externalId and SID (Security identifier) from the Users list page in D365FO or by running the following SQL against an environment in which they already exist:

DECLARE @existingUserId varchar(100) = 'Paul.Noakes'
SELECT 'exec #AddUserFromExisting '''
			+ @existingUserId + ''', ''' 
			+ ID + ''', ''' 
			+ NAME + ''', '''
			+ NETWORKALIAS + ''', '''
			+ EXTERNALID + ''', '''
			+ SID + ''''
FROM USERINFO
WHERE ENABLE = 1 and EXTERNALID != ''