Import-D365AadUser SQL error

[valerymoskalenko]

When I try to run
Import-D365AadUser -UserList $UserEmail -Verbose
I have got the following

...
VERBOSE: Import the user
VERBOSE: Adding User : glib.holovin@ciellos.com,Glib Holovin,glib.holovin,S-1-19-290.....192,DAT,https://sts.windows.net/,https://sts.windows.net/
VERBOSE: Creating the user in database
VERBOSE: Rows inserted  1 for user glib.holovin@ciellos.com
VERBOSE: Setting security roles in D365FO database
Exception calling "ExecuteScalar" with "0" argument(s): "Violation of PRIMARY KEY constraint 'I_65492RECIDIDX'. Cannot insert duplicate key in object 'dbo.SECURITYUSERROLE'. The duplicate key value is (5637244459).
The statement has been terminated."
At line:12 char:5
+     $differenceBetweenNewUserAndAdmin = $sqlCommand.ExecuteScalar()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
 
VERBOSE: Difference between new user and admin security roles 
Import-AadUserIntoD365FO : User glib.holovin@ciellos.com did not get securityRoles
At line:195 char:9
+         Import-AadUserIntoD365FO $SqlCommand $user.SignInName $name $ ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

Looks like SQL code is OK for AX2012, but for D365FFO you don't need anymore to retrieve the next RecId. Just insert a new row and SQL will automatically increment RecId field.

[fhoo]

That's interesting, I didn't noticed or heard of that. But it seems correct. For each table there is a new sequence in SQL Server and a constraint for the RecID field to default the value from the sequence.
And the SystemSequences table seems not to be used anymore.

[Splaxi]

Interesting!

@valerymoskalenko Could you try and update your .sql script file?

To open the correct sql script file

notepad (Join-Path "$((Get-Module d365fo.tools).Path | Split-Path -Parent)" "internal\sql\add-aaduserintod365fo.sql" )

Content to copy & paste into "add-aaduserintod365fo.sql"

  /*Variable input @Id,@SignInName,@Name,@SID, @StartUpCompany, @NetworkDomain, @IdentityProvider */

BEGIN TRANSACTION

begin TRY

DROP TABLE IF EXISTS #TempUser 

SET Nocount ON;

DECLARE @TableId AS int,
        @RecId AS bigint,
		@ExistsCompany as int

SELECT @ExistsCompany = count(1) 
  FROM [dbo].[DIRPARTYTABLE]
  join dbo.[PARTITIONS] p on p.RECID = DIRPARTYTABLE.PARTITION
  where DATAAREA = @StartUpCompany

  if(@ExistsCompany = 0)
	set @StartUpCompany ='dat'


	/* Get new recid for userInfo */
  /*
	SELECT @TableId = TableId
	FROM SQLDICTIONARY
	WHERE [NAME] = 'USERINFO'
	  AND FIELDID = 0 
  */

  /* We need to update SYSTEMSEQUENCES with the highest RECID in the table now*/
  /*
  Declare @IsSeqNumThere as int
  select @IsSeqNumThere = count(1) from SYSTEMSEQUENCES
  WHERE TABID = @TableId  AND [NAME] = 'SEQNO'

  if(@IsSeqNumThere = 1)
  begin

  UPDATE SYSTEMSEQUENCES
  SET NEXTVAL = (1 + (SELECT MAX(RECID) FROM USERINFO))
  WHERE TABID = @TableId AND [NAME] = 'SEQNO'

  SELECT @RecId = NEXTVAL
  FROM SYSTEMSEQUENCES WHERE TABID = @TableId
  AND [NAME] = 'SEQNO'
  
  UPDATE SYSTEMSEQUENCES
  SET NEXTVAL = @RecId +1 WHERE TABID = @TableId
  AND [NAME] = 'SEQNO'
  end

  if(@IsSeqNumThere = 0)
  begin
	SELECT @RecId = MAX(RECID)+1 FROM USERINFO
  end
*/

  
/* Get Admin to copy */
SELECT top 1 userInfo.* INTO #TempUser
FROM userinfo
JOIN [PARTITIONS] ON [PARTITIONS].Recid = userinfo.PARTITION
WHERE id = 'admin'
  AND PARTITIONKEY = 'initial'


/*Change row to match the new user */
UPDATE #TempUser
  SET 
    -- [RECID] = @RecId	  ,
    [ID] = @Id
	  ,[Name] = @Name
	  ,[SID] = @SID
	  ,[COMPANY] = @StartUpCompany
    ,[NETWORKALIAS] = @SignInName
	  ,RECVERSION = 1
    ,[NETWORKDOMAIN] = @NetworkDomain
    ,[IDENTITYPROVIDER] = @IdentityProvider
    ,[OBJECTID] = @ObjectId
    ,[EXTERNALID] = ''
    


/* Create the user */

INSERT INTO userinfo(ID, NAME, ENABLE, DEL_STARTUPMENU, STATUSLINEINFO, TOOLBARINFO, DEBUGINFO, AUTOINFO, AUTOUPDATE, GARBAGECOLLECTLIMIT, HISTORYLIMIT, MESSAGELIMIT, GENERALINFO, SHOWSTATUSLINE, SHOWTOOLBAR, DEBUGGERPOPUP, SHOWAOTLAYER, DEL_PASSWORD, DEL_OSACCOUNTNAME, STARTUPPROJECT, CONFIRMDELETE, CONFIRMUPDATE, REPORTFONTNAME, REPORTFONTSIZE, FORMFONTNAME, FORMFONTSIZE, PROPERTYFONTNAME, PROPERTYFONTSIZE, INFOLOGLEVEL, COMPANY, AUTOLOGOFF, QUERYTIMELIMIT, TRACEINFO, REPORTTOPMARGIN, REPORTBOTTOMMARGIN, REPORTLEFTMARGIN, REPORTRIGHTMARGIN, COMPILERWARNINGLEVEL, SID, NETWORKDOMAIN, NETWORKALIAS, ENABLEDONCE, EXTERNALUSER, LANGUAGE, HELPLANGUAGE, PREFERREDTIMEZONE, PREFERREDCALENDAR, HOMEPAGEREFRESHDURATION, NOTIFYTIMEZONEMISMATCH, FILTERBYGRIDONBYDEFAULT, GLOBALFORMOPENMODE, DEL_DEFAULTMODELID, SHOWMODELNAMEINAOT, ACCOUNTTYPE, ISSUERRECID, CREDENTIALRECID, GLOBALLISTPAGELINKMODE, GLOBALEXCELEXPORTMODE, CLIENTACCESSLOGLEVEL, DEFAULTPARTITION, GLOBALEXCELEXPORTFILEPATH, EXTERNALIDTYPE, EXTERNALID, RECVERSION, PARTITION, PREFERREDLOCALE, IDENTITYPROVIDER, OBJECTID, INTERACTIVELOGON, ISMICROSOFTACCOUNT)
	SELECT ID, NAME, ENABLE, DEL_STARTUPMENU, STATUSLINEINFO, TOOLBARINFO, DEBUGINFO, AUTOINFO, AUTOUPDATE, GARBAGECOLLECTLIMIT, HISTORYLIMIT, MESSAGELIMIT, GENERALINFO, SHOWSTATUSLINE, SHOWTOOLBAR, DEBUGGERPOPUP, SHOWAOTLAYER, DEL_PASSWORD, DEL_OSACCOUNTNAME, STARTUPPROJECT, CONFIRMDELETE, CONFIRMUPDATE, REPORTFONTNAME, REPORTFONTSIZE, FORMFONTNAME, FORMFONTSIZE, PROPERTYFONTNAME, PROPERTYFONTSIZE, INFOLOGLEVEL, COMPANY, AUTOLOGOFF, QUERYTIMELIMIT, TRACEINFO, REPORTTOPMARGIN, REPORTBOTTOMMARGIN, REPORTLEFTMARGIN, REPORTRIGHTMARGIN, COMPILERWARNINGLEVEL, SID, NETWORKDOMAIN, NETWORKALIAS, ENABLEDONCE, EXTERNALUSER, LANGUAGE, HELPLANGUAGE, PREFERREDTIMEZONE, PREFERREDCALENDAR, HOMEPAGEREFRESHDURATION, NOTIFYTIMEZONEMISMATCH, FILTERBYGRIDONBYDEFAULT, GLOBALFORMOPENMODE, DEL_DEFAULTMODELID, SHOWMODELNAMEINAOT, ACCOUNTTYPE, ISSUERRECID, CREDENTIALRECID, GLOBALLISTPAGELINKMODE, GLOBALEXCELEXPORTMODE, CLIENTACCESSLOGLEVEL, DEFAULTPARTITION, GLOBALEXCELEXPORTFILEPATH, EXTERNALIDTYPE, EXTERNALID, RECVERSION, PARTITION, PREFERREDLOCALE, IDENTITYPROVIDER, OBJECTID, INTERACTIVELOGON, ISMICROSOFTACCOUNT FROM #TempUser

DROP TABLE #TempUser

COMMIT TRANSACTION

end TRY

begin CATCH 
ROLLBACK TRANSACTION

end CATCH

SET Nocount OFF;
select count(1) from userinfo
where [RECID] = (SELECT MAX(Recid) FROM dbo.USERINFO)

[valerymoskalenko]

Hi, Looks like wrong SQL script. I have no issues when inserting a new user into USERINFO table. I have an issue on inserting into SECURITYUSERROLE table.

Please find the corrected SQL script Set-AadUserSecurityInD365FO.sql below

/*Variable input @Id */
DROP TABLE IF EXISTS #TempSecurityUserRole
DROP TABLE IF EXISTS #TempRecIds

BEGIN TRANSACTION

SET NOCOUNT ON;

DECLARE @TableId AS int
        ,@RecId AS bigint
		,@AdminUserId as NVARCHAR(40) = 'Admin'
		,@InitialPartionKey as nvarchar(10) = 'initial'


SELECT 
	--SECURITYUSERROLE.USER_,
	@Id as USER_,
	SECURITYUSERROLE.SECURITYROLE,
	SECURITYUSERROLE.ASSIGNMENTSTATUS,
	SECURITYUSERROLE.ASSIGNMENTMODE,
	SECURITYUSERROLE.VALIDFROM,
	SECURITYUSERROLE.VALIDFROMTZID,
	SECURITYUSERROLE.VALIDTO,
	SECURITYUSERROLE.VALIDTOTZID,
	SECURITYUSERROLE.PARTITION
INTO #TempSecurityUserRole
FROM SECURITYUSERROLE
JOIN [PARTITIONS] ON [PARTITIONS].Recid = SECURITYUSERROLE.PARTITION
WHERE [User_] = @AdminUserId
	AND PARTITIONKEY = @InitialPartionKey

/*
SELECT [RECID] 
INTO #TempRecIds
FROM #TempSecurityUserRole

SELECT @TableId = TableId
FROM SQLDICTIONARY
WHERE [NAME] = 'SECURITYUSERROLE'
AND FIELDID = 0 
*/

/* We need to update SYSTEMSEQUENCES with the highest RECID in the table now*/
/*
UPDATE SYSTEMSEQUENCES
SET NEXTVAL = (1 + (SELECT MAX(RECID) FROM SECURITYUSERROLE))
WHERE TABID = @TableId AND [NAME] = 'SEQNO'

 Declare @IsSeqNumThere as int
select @IsSeqNumThere = count(1) from SYSTEMSEQUENCES
  WHERE TABID = @TableId  AND [NAME] = 'SEQNO'

DECLARE @CurrentRowId AS BIGINT
 
SET @CurrentRowId = (SELECT MIN(RECID) FROM #TempRecIds)
 
WHILE @CurrentRowId IS NOT NULL
BEGIN

	if(@IsSeqNumThere = 1)
	BEGIN

		SELECT @RecId = NEXTVAL
		FROM SYSTEMSEQUENCES WHERE TABID = @TableId
		AND [NAME] = 'SEQNO'
	
		UPDATE SYSTEMSEQUENCES
		SET NEXTVAL = @RecId +1 WHERE TABID = @TableId
		AND [NAME] = 'SEQNO'
		END
	if(@IsSeqNumThere = 0)
	BEGIN
		SELECT @RecId = MAX(RECID)+1 FROM SECURITYUSERROLE
	end

	UPDATE #TempSecurityUserRole
	SET 
	[RECID] = @RecId
	,[User_] = @Id
	WHERE [RECID] = @CurrentRowId
	
	SET @CurrentRowId = (SELECT MIN(RECID) FROM #TempRecIds WHERE RECID > @CurrentRowId)
END
*/

INSERT INTO SECURITYUSERROLE (
	USER_,
	SECURITYROLE,
	ASSIGNMENTSTATUS,
	ASSIGNMENTMODE,
	VALIDFROM,
	VALIDFROMTZID,
	VALIDTO,
	VALIDTOTZID,
	PARTITION
)
SELECT * FROM #TempSecurityUserRole

DROP TABLE #TempSecurityUserRole
/* DROP TABLE #TempRecIds 
*/

commit TRANSACTION

Declare @AdminSecurityRoleCount as int

SELECT @AdminSecurityRoleCount = count(1) 
FROM SECURITYUSERROLE 
JOIN [PARTITIONS] ON [PARTITIONS].Recid = SECURITYUSERROLE.PARTITION
WHERE [User_] = @AdminUserId
AND PARTITIONKEY = @InitialPartionKey

Declare @ImportSecurityRoleCount as int

SELECT @ImportSecurityRoleCount = count(1) 
FROM SECURITYUSERROLE 
JOIN [PARTITIONS] ON [PARTITIONS].Recid = SECURITYUSERROLE.PARTITION
WHERE [User_] = @Id
AND PARTITIONKEY =  @InitialPartionKey

SET Nocount OFF;

select @AdminSecurityRoleCount - @ImportSecurityRoleCount

[ITRasmus]

Moved to #38 as a general fix for every insert with the tool

[harishmohanbabu]

I get below exception -

[Import-D365AadUser] Something went wrong while working against the database | You cannot call a method on a null-valued expression.

The command I ran is below:
Import-D365AadUser -User <User email> -Verbose

Am I missing something obvious? Please advise.

[ITRasmus]

Hi

The Command uses either -userlist or -aadgroupname. Try using the get-help
See more here https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/get-help?view=powershell-6
Next time you are more than welcome to create a new issue instead of using an old one

[Splaxi]

@harishmohanbabu

Import-D365AadUser -Users "Claire@contoso.com","Allen@contoso.com"

That is the correct syntax. Please make sure you use the latest module,

When in doubt about any command, simply put Get-Help in front of any command. E.g.
Get-Help Import-D365AadUser