USP_RECORDOPERATION_APPUSER_CONVERT_PROXYUSER

            CREATE procedure dbo.USP_RECORDOPERATION_APPUSER_CONVERT_PROXYUSER
            (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @PROXYOWNERID uniqueidentifier
            )
            as
                set nocount on;

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

                declare @CURRENTDATE datetime = getdate();
                declare @ISPROXYUSER bit = 0;
                declare @ISSYSADMIN bit = 0;
                declare @DISPLAYNAME nvarchar(255);

                select @DISPLAYNAME = DISPLAYNAME from dbo.APPUSER where ID = @ID;

                select
                    @ISPROXYUSER = isnull(APPUSER.ISPROXYUSER, 0)
                    from dbo.APPUSER
                    where APPUSER.ID = @ID;

                select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@PROXYOWNERID)

                if @ISPROXYUSER = 1
                    raiserror('BBERR_APPUSERALREADYCONVERTEDTOPROXYUSER', 13, 1);

                --error raised when appuser to be converted is a proxy owner

                if((select count(1) from dbo.APPUSER where PROXYOWNERID = @ID) > 0)
                    begin
                        raiserror('BBERR_APPUSERISPROXYOWNER', 13, 1);
                        return;
                    end

                --error raised when appuser tries to convert themselves

                if @ID = @PROXYOWNERID
                    begin
                        raiserror('BBERR_APPUSERCANNOTCONVERTTHEMSELVES', 13, 1);
                        return;
                    end

                --To prevent the windows user to be converted to proxy user again

                    if exists(select CUSTOM_AUTHENTICATION_USERID from dbo.APPUSER where USERSID = suser_sid('NT AUTHORITY\ANONYMOUS LOGON') and DISPLAYNAME = @DISPLAYNAME and isnull(ISPROXYUSER, 0) = 1)
                        begin
                            raiserror('BBERR_APPUSERCANNOTBECONVERTEDAGAIN', 13, 1);
                            return;
                        end

                --Check if the proxy owner is a system admin, if no then roles/permissions checking is performed and conversion happens

                if @ISSYSADMIN = 0
                    begin
                        --A proxy user cannot have system roles permissions different from proxy owner.

                        --Check to see if this is the case, if yes throw an error.


                        declare @PROXYOWNERROLES table (SYSTEMROLEID uniqueidentifier)
                        insert into @PROXYOWNERROLES
                        select SYSTEMROLEID from SYSTEMROLEAPPUSER 
                            where APPUSERID = @PROXYOWNERID and SYSTEMROLEID not in (select SYSTEMROLEID from SYSTEMROLEAPPUSER where APPUSERID = @ID);

                        --when system roles of proxyuser are same/subset of proxyowner, we will perform conversion

                        if((select count(1) from (select SYSTEMROLEID from SYSTEMROLEAPPUSER where APPUSERID = @ID
                                                    and SYSTEMROLEID not in (select SYSTEMROLEID from SYSTEMROLEAPPUSER where APPUSERID = @PROXYOWNERID)) AS PROXYUSERROLES) = 0)
                            begin try
                                update dbo.APPUSER
                                set
                                    ISPROXYUSER = 1,
                                    HASRUNASRIGHTS = 0,
                                    PROXYOWNERID = @PROXYOWNERID,
                                    CUSTOM_AUTHENTICATION_USERID = suser_sname([USERSID]),
         USERSID = suser_sid('NT AUTHORITY\ANONYMOUS LOGON'),
                                    EMAILADDRESS = NULL,
                                    INVALIDLOGINATTEMPTS = 0,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where ID = @ID;

                                --Copy roles/permissions from PROXYOWNER

                                exec dbo.USP_DATAFORMTEMPLATE_EDIT_APPLICATIONUSERCOPYROLES @ID, @PROXYOWNERID, @CHANGEAGENTID;

                                delete from dbo.SYSTEMROLEAPPUSER 
                                where SYSTEMROLEID in (select SYSTEMROLEID from @PROXYOWNERROLES)
                                and APPUSERID = @ID;
                            end try

                            begin catch
                            raiserror('BBERR_APPUSERNOTVERIFIEDFROMACTIVEDIRECTORY', 13, 1)
                          end catch

                            --when permissions of proxyuser are higher than proxyowner

                        else
                            begin
                                raiserror('BBERR_APPUSERCONVERTEDTOPROXYUSERHIGHERPERMISSIONS', 13, 1);
                            end
                    end
                --Conversion without checking of roles/permissions when proxy owner is a system admin

                else
                    begin try
                        update dbo.APPUSER
                        set
                            ISPROXYUSER = 1,
                            HASRUNASRIGHTS = 0,
                            PROXYOWNERID = @PROXYOWNERID,
                            CUSTOM_AUTHENTICATION_USERID = suser_sname([USERSID]),
                            USERSID = suser_sid('NT AUTHORITY\ANONYMOUS LOGON'),
                            EMAILADDRESS = NULL,
                            INVALIDLOGINATTEMPTS = 0,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @ID;
                    end try

                    begin catch
                    raiserror('BBERR_APPUSERNOTVERIFIEDFROMACTIVEDIRECTORY', 13, 1)
                  end catch

                return 0;