USP_SYSTEMROLEAPPUSER_DELETE

Executes the "System Role: Remove User from role" record operation.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being deleted.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the delete.

Definition

Copy



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

set nocount on;

    declare @PROXYCOUNT tinyint = 0;
    declare @LISTCOUNT tinyint = 1;
    declare @SYSTEMROLEID uniqueidentifier;
    declare @PROXYUSERIDLIST table (PROXYCOUNT int identity(1,1), PROXYSYSTEMROLEAPPUSERID uniqueidentifier);
    declare @APPUSERID uniqueidentifier;
    declare @SYSTEMROLEAPPUSERIDPROXY uniqueidentifier;
    declare @PROXYOWNERID uniqueidentifier;
    declare @ISSYSADMIN bit = 0;

    select @PROXYCOUNT = count(1) from dbo.APPUSER join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.APPUSERID =APPUSER.PROXYOWNERID where SYSTEMROLEAPPUSER.ID = @ID;
    select @PROXYOWNERID = APPUSER.PROXYOWNERID from dbo.APPUSER join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.APPUSERID = APPUSER.PROXYOWNERID where SYSTEMROLEAPPUSER.ID = @ID;
    select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@PROXYOWNERID);

    select
      @SYSTEMROLEID = SYSTEMROLEID,
      @APPUSERID = APPUSERID
    from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEAPPUSER.ID = @ID;

    --if proxy owner is not a system admin then delete the corresponding proxy user roles

    if @ISSYSADMIN = 0
        begin
            if @PROXYCOUNT > 0
                begin
                    insert into @PROXYUSERIDLIST
                    select
                      SYSTEMROLEAPPUSER.ID
                    from dbo.SYSTEMROLEAPPUSER
                    inner join dbo.APPUSER on APPUSER.ID = SYSTEMROLEAPPUSER.APPUSERID
                    where APPUSER.PROXYOWNERID = @APPUSERID and SYSTEMROLEAPPUSER.SYSTEMROLEID = @SYSTEMROLEID;

                    while (@LISTCOUNT <= @PROXYCOUNT)
                    begin
                        select @SYSTEMROLEAPPUSERIDPROXY = PROXYLIST.PROXYSYSTEMROLEAPPUSERID from @PROXYUSERIDLIST PROXYLIST where PROXYLIST.PROXYCOUNT = @LISTCOUNT;

                        exec dbo.USP_SYSTEMROLEAPPUSER_DELETEBYID_WITHCHANGEAGENTID @SYSTEMROLEAPPUSERIDPROXY, @CHANGEAGENTID;

                        set @LISTCOUNT = @LISTCOUNT + 1;
                    end
                end
        end
    exec dbo.USP_SYSTEMROLEAPPUSER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
return 0;