USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLE_ASSIGNUSERS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLE_ASSIGNUSERS

@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@USERSIDLIST xml

 AS

set nocount on;

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE=getdate();

BEGIN TRY

    update dbo.SYSTEMROLE set
        CHANGEDBYID = @CHANGEAGENTID  ,DATECHANGED=@CURRENTDATE
    where ID = @ID;

    --Parse and validate user name list

    declare @sids table (sid varbinary(85), username nvarchar(255))
    insert into @sids (sid,username)
        select distinct USERSID, DISPLAYNAME from dbo.[UFN_SYSTEMROLE_GETUSERSIDLIST_FROMITEMLISTXML](@USERSIDLIST);

    --Add Appusers that don't yet exist

    insert into dbo.APPUSER (USERSID, [DISPLAYNAME], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], WINDOWSUSERNAME)
        select distinct SID,COALESCE(USERNAME,''),@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,
            case when len(NEWUSERS.USERNAME) > 0 then NEWUSERS.USERNAME else coalesce(suser_name(SID), '') end
        from @sids as NEWUSERS
        where NEWUSERS.SID not in (select USERSID from dbo.APPUSER);

    --Remove users no longer in role

    delete from dbo.SYSTEMROLEAPPUSER
        where SYSTEMROLEID=@ID and
        FROMROLESYNC = 0 and
        APPUSERID not in 
            (select AU.ID from @sids as s inner join dbo.APPUSER as AU on  s.sid =AU.USERSID);

    --Add users to role

    insert into dbo.SYSTEMROLEAPPUSER (SYSTEMROLEID, APPUSERID, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
        select @id, APPUSER.ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.APPUSER
        where APPUSER.USERSID in (select SID from @sids)
        and APPUSER.ID not in (select APPUSERID from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEID=@ID);

    --Update display names for any Appusers that don't have them

    update dbo.APPUSER set DISPLAYNAME = NEWUSERS.USERNAME
    from dbo.APPUSER
        inner join @sids as NEWUSERS
        on APPUSER.USERSID = NEWUSERS.SID
    where len(APPUSER.DISPLAYNAME) = 0 and len(NEWUSERS.USERNAME) > 0;

    --Update the "synchronized" flag for users that have been entered

    update dbo.SYSTEMROLEAPPUSER set FROMROLESYNC = 0
    from dbo.SYSTEMROLEAPPUSER
        inner join dbo.APPUSER on SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
        inner join @sids as NEWUSERS on APPUSER.USERSID = NEWUSERS.SID
    where SYSTEMROLEID = @ID and SYSTEMROLEAPPUSER.FROMROLESYNC = 1;

    return 0;

END TRY
BEGIN CATCH
    exec dbo.USP_RAISE_ERROR;
    return 1;
END CATCH