Add permissions for additional branches

Clearinghouse Repository Polaris Repository
,
1

This script will only insert Circulation subsystem permissions. I’ve tried to genericize the script the best I could while still keeping some the specific pieces I felt could be applicable to other libraries. You can comment out the first line and the ) d line to run a select only version first to review what will be inserted.

begin tran insert into polaris.polaris.permissiongroups select d.ControlRecordID, d.PermissionID, d.GroupID from (

select distinct g.GroupID, g.GroupName, pp_new.PermissionID, pp_new.ControlRecordID, pp_new.ControlRecordName, pp_new.PermissionName, pp_new.OrganizationID, o_new.Name, pp_new.ControlRecordDefID, pp_new.PermissionNameID, pp_source.SubSystem
from Polaris.Polaris.Groups g
join polaris.Polaris.PermissionGroups pg
	on pg.GroupID = g.GroupID
join Polaris.dbo.CLC_Custom_PolarisPermissions pp_source
	on pp_source.SubSystemID = 2 and pp_source.ControlRecordID = pg.ControlRecordID and pp_source.PermissionID = pg.PermissionID
join Polaris.Polaris.Organizations o_source
	on o_source.OrganizationID = pp_source.OrganizationID
join Polaris.dbo.CLC_Custom_PolarisPermissions pp_new
	on pp_new.ControlRecordName not like '%patron record sets%' and pp_new.ControlRecordDefID = pp_source.ControlRecordDefID and pp_new.PermissionNameID = pp_source.PermissionNameID and pp_new.OrganizationID != pp_source.OrganizationID
join polaris.Polaris.Organizations o_new
	on o_new.OrganizationID = pp_new.OrganizationID
where pp_source.SubSystemID = 2
	-- permission not already granted
	and not exists ( select 1 from Polaris.Polaris.PermissionGroups _pg where _pg.GroupID = g.GroupID and _pg.ControlRecordID = pp_new.ControlRecordID and _pg.PermissionID = pp_new.PermissionID )
	-- only add permissions to valid groups
	and g.GroupID >= 22
	-- exclude 'deny item request'
	and pp_new.PermissionNameID not in (35)
) d

--rollback
--commit
1 Like
2

Forgot that this relies on a helper view we’ve got. Here’s the definition for that:

USE [Polaris]
GO

/****** Object:  View [dbo].[CLC_Custom_PolarisPermissions]    Script Date: 10/11/2023 4:26:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE view [dbo].[CLC_Custom_PolarisPermissions]
as
select cr.ControlRecordID, pd.PermissionID, crd.SubSystemID, pss.Description [Subsystem], crd.ControlRecordDefID, crd.ControlRecordName, pd.PermissionNameID, pn.PermissionName, cr.OrganizationID, crd.SystemFlag, crd.LibraryFlag, crd.BranchFlag
from Polaris.Polaris.ControlRecords cr
join Polaris.Polaris.ControlRecordDefs crd on
	cr.ControlRecordDefID = crd.ControlRecordDefID
join Polaris.Polaris.PermissionDefs pd on
	pd.ControlRecordDefID = crd.ControlRecordDefID
join Polaris.Polaris.PermissionNames pn on
	pd.PermissionNameID = pn.PermissionNameID
join Polaris.Polaris.SA_PermissionSubsystems pss on
	pss.SubsystemID = crd.SubSystemID
GO
1 Like