USP_SYSTEMROLEAPPUSER_REVOKESYSADMIN

CREATE procedure dbo.USP_SYSTEMROLEAPPUSER_REVOKESYSADMIN
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as

    declare @ISAPPUSERPROXYOWNER bit;
    declare @PROXYOWNERSYSTEMROLEAPPUSERID table (SYSTEMROLEAPPUSERID uniqueidentifier);
    declare @PROXYUSERSYSTEMROLEID table (ROLEID uniqueidentifier);
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    select @ISAPPUSERPROXYOWNER = dbo.UFN_APPUSER_HASPROXYUSER(@ID);

    if(@ISAPPUSERPROXYOWNER = 1)
        begin

             --Get existing roles of proxy owner to delete SITES and CONSTITUENTSECURITYS and update SITE and CONSTITUENTSECURITY mode

                insert into @PROXYOWNERSYSTEMROLEAPPUSERID
                select ID from dbo.SYSTEMROLEAPPUSER where APPUSERID = @ID;

             --Get roles of all the proxy users and exclude existing roles of proxy owner

                insert into @PROXYUSERSYSTEMROLEID
                select SYSTEMROLEID from dbo.SYSTEMROLEAPPUSER
                where APPUSERID in (select ID from dbo.APPUSER where PROXYOWNERID = @ID) 
                except
                select SYSTEMROLEID from dbo.SYSTEMROLEAPPUSER where APPUSERID = @ID;

             --update existing roles (SITE and CONSTITUENTSECURITY mode) of proxy owner 

                update
                    dbo.SYSTEMROLEAPPUSER
                set
                    SYSTEMROLEAPPUSER.SECURITYMODECODE = 0,
                    SYSTEMROLEAPPUSER.DATECHANGED = @CURRENTDATE,
                    SYSTEMROLEAPPUSER.CHANGEDBYID = @CHANGEAGENTID,
                    SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = 0
                where
                    ID in (select SYSTEMROLEAPPUSERID from @PROXYOWNERSYSTEMROLEAPPUSERID);

                declare @contextcache varbinary(128) = CONTEXT_INFO();
                set CONTEXT_INFO @CHANGEAGENTID;

             -- Clear the sites collection of existing roles for proxy owner

                delete from dbo.[SYSTEMROLEAPPUSERSITE] where [SYSTEMROLEAPPUSERSITE].ID in 
                (select SYSTEMROLEAPPUSERSITE.ID from SYSTEMROLEAPPUSERSITE 
                 inner join @PROXYOWNERSYSTEMROLEAPPUSERID PROXYOWNERSYSTEMROLEAPPUSERID 
                 on SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID = PROXYOWNERSYSTEMROLEAPPUSERID.SYSTEMROLEAPPUSERID)

            -- Clear constituent security groups of existing roles for proxy owner

                delete from dbo.[SYSTEMROLEAPPUSERCONSTITUENTSECURITY] where [SYSTEMROLEAPPUSERCONSTITUENTSECURITY].ID in   
                (select ID from SYSTEMROLEAPPUSERCONSTITUENTSECURITY 
                 inner join @PROXYOWNERSYSTEMROLEAPPUSERID PROXYOWNERSYSTEMROLEAPPUSERID 
                 on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = PROXYOWNERSYSTEMROLEAPPUSERID.SYSTEMROLEAPPUSERID)

                if @contextcache is not null
                   begin
                        set CONTEXT_INFO @contextcache;
                   end

            --insert Proxy users roles for proxy owner with All Records SITE and CONSTITUENT security settings

                insert into dbo.SYSTEMROLEAPPUSER(APPUSERID, SYSTEMROLEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE, ACCOUNTINGELEMENTSECURITYMODECODE)
                select @ID, ROLEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 0, NULL, 0, 0 
                from @PROXYUSERSYSTEMROLEID;
        end

exec dbo.USP_APPUSER_SETSYSADMIN @ID, @CHANGEAGENTID, 0

return 0;