Roblog

create function dbo.GetPrivilegeDepthMask(@isbasic bit, @islocal bit, @isdeep bit, @isglobal bit, @parentRoleId uniqueidentifier)returns int as
begin
            declare @mask int
            select @mask = 0          

            if (@isbasic <> 0)
            begin
                        if (@parentRoleId is null)
                        begin
                                   select @mask = 1
                        end
                        if (@parentRoleId is not null)
                        begin
                                   select @mask = 0x00000010
                        end
            end
            if (@islocal <> 0)
            begin
                        if (@parentRoleId is null)
                        begin
                                   select @mask = 2
                        end
                        if (@parentRoleId is not null)
                        begin
                                   select @mask = 0x00000020
                        end
            end
            if (@isdeep <> 0)
            begin
                        if (@parentRoleId is null)
                        begin
                                   select @mask = 4
                        end
                        if (@parentRoleId is not null)
                        begin
                                   select @mask = 0x00000040
                        end
            end
            if (@isglobal <> 0)
            begin
                        if (@parentRoleId is null)
                        begin
                                   select @mask = 8
                        end
                        if (@parentRoleId is not null)
                        begin
                                   select @mask = 0x00000080
                        end
            end
            return @mask
end
go

declare @rootBiz uniqueidentifier
declare @rootId uniqueidentifier
declare @organizationid uniqueidentifier
declare @roleTemplateId uniqueidentifier
select @roleTemplateId = '119F245C-3CC8-4B62-B31C-D1A046CED15D'
declare @roleTemplateName nvarchar(256)
select @roleTemplateName = Name from RoleTemplateBase where RoleTemplateId = @roleTemplateId
declare @parentRoleId uniqueidentifier
select @parentRoleId = null
declare @roleid uniqueidentifier
select @roleid = null
select @rootBiz = BusinessUnitId from BusinessUnitBase where
ParentBusinessUnitId is null
declare c cursor FORWARD_ONLY READ_ONLY for select businessunitid from
dbo.GetSubsidiaryBusinesses(@rootBiz) order by depth
open c
fetch next from c into @rootId
while (@@fetch_status = 0)
begin
            -- Get ParentRoleId
            select @parentRoleId = RoleId from RoleBase where BusinessUnitId = (select ParentBusinessUnitId from BusinessUnitBase where BusinessUnitId = @rootId) and RoleTemplateId = @roleTemplateId
            select @roleid = RoleId from RoleBase where BusinessUnitId = @rootId and RoleTemplateId = @roleTemplateId
            if (@roleid is null)
            begin
                        select @roleid = newid()
                        insert into RoleBase(RoleId, RoleTemplateId, OrganizationId, DeletionStateCode, Name, BusinessUnitId, CreatedOn, ModifiedOn, CreatedBy, ModifiedBy, ParentRoleId)
                        values(@roleid, @roleTemplateId, @organizationid, 0, @roleTemplateName, @rootId, getutcdate(), getutcdate(), null, null, @parentRoleId)  
            end
            delete from RolePrivileges where RoleId = @roleid
            insert into RolePrivileges(RoleId, PrivilegeId, PrivilegeDepthMask)
                        select @roleid, rtp.PrivilegeId, dbo.GetPrivilegeDepthMask(rtp.IsBasic, rtp.IsLocal, rtp.IsDeep, rtp.IsGlobal, @parentRoleId)
                        from RoleTemplatePrivileges rtp
                        where rtp.RoleTemplateId = @roleTemplateId
            fetch next from c into @rootId
            select @roleid = null
end
close c
deallocate c