USP_ROLESYNC_SYSTEMROLE_SYNCUSERS

            CREATE procedure dbo.USP_ROLESYNC_SYSTEMROLE_SYNCUSERS 
            (
                @SYSTEMROLEID uniqueidentifier, 
                @CHANGEAGENTID uniqueidentifier = null, 
                @USERSIDLIST xml
            )
            as
                --Used by the RoleSync utility

                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 = @SYSTEMROLEID;

                    --Parse and validate user name list

                    declare @sids table (SYSTEMROLEAPPUSERID uniqueidentifier, sid varbinary(85), username nvarchar(255), groupid uniqueidentifier)

                    insert into @sids (SYSTEMROLEAPPUSERID, sid, username, groupid)
                        select 
                            isnull(
                                (select 
                                    SYSTEMROLEAPPUSER.ID 
                                from 
                                    dbo.SYSTEMROLEAPPUSER 
                                    inner join dbo.APPUSER on APPUSER.ID = SYSTEMROLEAPPUSER.APPUSERID 
                                where 
                                    APPUSER.USERSID = T.c.value('(USERSID)[1]','varbinary(85)') and 
                                    SYSTEMROLEAPPUSER.SYSTEMROLEID = @SYSTEMROLEID
                                ), 
                                newID()
                            ) as ID,
                            T.c.value('(USERSID)[1]','varbinary(85)') as USERSID,
                            T.c.value('(DISPLAYNAME)[1]','nvarchar(255)') as DISPLAYNAME,
                            T.c.value('(GROUPID)[1]','uniqueidentifier') as GROUPID
                        from 
                            @USERSIDLIST.nodes('/USERSIDLIST/ITEM') T(c);

                    --Add Appusers that don't yet exist

                    insert into dbo.APPUSER (USERSID, [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], WINDOWSUSERNAME)
                        select distinct 
                            SID,
                            @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);

                    declare @contextCache varbinary(128);

                    /* Cache current context information 
                    The Context Info will be used by delete triggers to log the AUDITCHANGEAGENTID 
                    as the supplied @CHANGEAGENTID rather than the default change agent id. */

                    set @contextCache = CONTEXT_INFO();

                    --Set CONTEXT_INFO to @CHANGEAGENTID

                    if not @CHANGEAGENTID is null
                        set CONTEXT_INFO @CHANGEAGENTID

                    --Remove users no longer in role

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

                    --Reset CONTEXT_INFO to previous value 

                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache                    

                    --Cache the list of records with security updates

                    declare @SECURITYUPDATES table(SYSTEMROLEAPPUSERID uniqueidentifier, GROUPID uniqueidentifier, SECURITYMODECODE tinyint, BRANCHSITEID uniqueidentifier, CONSTITUENTSECURITYMODECODE tinyint);

                    insert into @SECURITYUPDATES(SYSTEMROLEAPPUSERID, GROUPID, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE)
                        select
                            SIDS.SYSTEMROLEAPPUSERID,
                            SYSTEMROLEADGROUP.ID,
                            SYSTEMROLEADGROUP.SECURITYMODECODE,
                            SYSTEMROLEADGROUP.BRANCHSITEID,
                            SYSTEMROLEADGROUP.CONSTITUENTSECURITYMODECODE
                        from
                            dbo.APPUSER
                            inner join @sids as SIDS on SIDS.SID = APPUSER.USERSID
                            inner join dbo.SYSTEMROLEADGROUP on SYSTEMROLEADGROUP.ID = SIDS.GROUPID
                        where 
                            APPUSER.ID not in (select APPUSERID from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEID = @SYSTEMROLEID) or
                            SYSTEMROLEADGROUP.OVERWRITEEXISTINGSECURITY = 1

                    --Add users to role

                    insert into dbo.SYSTEMROLEAPPUSER (ID, SYSTEMROLEID, APPUSERID, FROMROLESYNC, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                        select 
                            SIDS.SYSTEMROLEAPPUSERID,
                            @SYSTEMROLEID, 
                            APPUSER.ID, 
                            1, 
                            isnull((select SYSTEMROLEADGROUP.SECURITYMODECODE from dbo.SYSTEMROLEADGROUP WHERE SIDS.GROUPID = SYSTEMROLEADGROUP.ID),0),
                            (select SYSTEMROLEADGROUP.BRANCHSITEID from dbo.SYSTEMROLEADGROUP where SIDS.GROUPID = SYSTEMROLEADGROUP.ID),
                            isnull((select SYSTEMROLEADGROUP.CONSTITUENTSECURITYMODECODE from dbo.SYSTEMROLEADGROUP WHERE SIDS.GROUPID = SYSTEMROLEADGROUP.ID),0),
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID, 
                            @CURRENTDATE, 
                            @CURRENTDATE
                        from 
                            dbo.APPUSER
                            inner join @sids as SIDS on SIDS.SID = APPUSER.USERSID
                        where 
                            APPUSER.ID not in (select APPUSERID from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEID = @SYSTEMROLEID);

                    --Set site and constituent security on synchronized records

                    update 
                        dbo.SYSTEMROLEAPPUSER
                    set
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = SECURITYUPDATES.SECURITYMODECODE,
                        SYSTEMROLEAPPUSER.BRANCHSITEID = SECURITYUPDATES.BRANCHSITEID,
                        SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = SECURITYUPDATES.CONSTITUENTSECURITYMODECODE,
                        FROMROLESYNC = 1
                    from
                        SYSTEMROLEAPPUSER
                        inner join @SECURITYUPDATES as SECURITYUPDATES on SECURITYUPDATES.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID                        

                    /* Cache current context information 
                       The Context Info will be used by delete triggers to log the AUDITCHANGEAGENTID 
                       as the supplied @CHANGEAGENTID rather than the default change agent id. */
                    set @contextCache = CONTEXT_INFO();

                    --Set CONTEXT_INFO to @CHANGEAGENTID

                    if not @CHANGEAGENTID is null
                        set CONTEXT_INFO @CHANGEAGENTID

                    --Remove any existing site security.  This will be overwritten with site security defined on the group.

                    delete from 
                        dbo.SYSTEMROLEAPPUSERSITE
                    where 
                        SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID in (select SYSTEMROLEAPPUSERID from @SECURITYUPDATES);

                    --Remove any existing constituent security.  This will be overwritten with constituent security defined on the group.

                    delete from 
                        dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY
                    where 
                        SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID in (select SYSTEMROLEAPPUSERID from @SECURITYUPDATES);

                    --Reset CONTEXT_INFO to previous value 

                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache

                    --Insert site security

                    insert into dbo.SYSTEMROLEAPPUSERSITE (SYSTEMROLEAPPUSERID, SITEID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                        select 
                            SECURITYUPDATES.SYSTEMROLEAPPUSERID, 
                            SYSTEMROLEADGROUPSITE.SITEID, 
                            @CURRENTDATE, 
                            @CURRENTDATE, 
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID 
                        from 
                            @SECURITYUPDATES as SECURITYUPDATES 
                            inner join dbo.SYSTEMROLEADGROUPSITE on SYSTEMROLEADGROUPSITE.SYSTEMROLEADGROUPID = SECURITYUPDATES.GROUPID
                        where
                            SECURITYUPDATES.SECURITYMODECODE = 2;

                    --Insert constituent security

                    insert into dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY(SYSTEMROLEAPPUSERID, CONSTITUENTSECURITYATTRIBUTEID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                        select 
                            SECURITYUPDATES.SYSTEMROLEAPPUSERID, 
                            SYSTEMROLEADGROUPCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID, 
                            @CURRENTDATE, 
                            @CURRENTDATE, 
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID 
                        from 
                            @SECURITYUPDATES as SECURITYUPDATES 
                            inner join dbo.SYSTEMROLEADGROUPCONSTITUENTSECURITY on SYSTEMROLEADGROUPCONSTITUENTSECURITY.SYSTEMROLEADGROUPID = SECURITYUPDATES.GROUPID
                        where
                            SECURITYUPDATES.CONSTITUENTSECURITYMODECODE = 2

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

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

                    return 0;

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