This is nothing more than a modification of the sproc that powers the Staff Member permissions report. The problem with that report is, if you need to get permissions for several staff members, you’re going to be doing a lot of clicking and saving running the same report over and over again.
With this query, you can set initial parameters and then simply change the PolarisUserIDs as needed to get your lists. It’s much faster.
DECLARE @nUserID INT = 2741; -- PolarisUserID
DECLARE @sOrganizationList NVARCHAR(MAX) = '44'; -- Provide a comma separated list of desired OrganizationIDs
DECLARE @sSubSystemList NVARCHAR(MAX) = '1,2,3,4,5,6,7,8,9'; -- Provide a list of subsystems. As of Polaris 7.4 there are 9 of them.
-- A list of subsystems can be obtained via:
--SELECT * FROM Polaris.Polaris.SA_PermissionSubsystems WITH (NOLOCK)
DECLARE @nBranchID INT
-- Collect Passed-In Branch IDs
DECLARE @tblBranches TABLE (BranchID INT NOT NULL)
INSERT INTO -- Parse Comma-Separate List Of Orgs
@tblBranches (BranchID)
SELECT
CAST(SubstringValue AS INT)
FROM
Polaris.Polaris.fn_SA_GetTableOfValuesFromCommaDelimitedList(@sOrganizationList)
DECLARE @nCount INT = 0
SELECT @nCount = COUNT(*) FROM @tblBranches
IF (@nCount = 1) -- Only 1 Number In List?
BEGIN
SELECT @nBranchID = BranchID FROM @tblBranches
IF (@nBranchID = 0) -- Single Value = 0 --> All Selected
BEGIN
DELETE FROM @tblBranches -- Clear Out 0 Row
INSERT INTO -- Collect All Branch IDs
@tblBranches (BranchID)
SELECT
OrganizationID
FROM
Polaris.Polaris.Organizations WITH (NOLOCK)
WHERE
OrganizationCodeID = 3
END
END
-- Collect Passed-In Subsystem IDs
DECLARE @tblSubsystems TABLE (SubsystemID INT NOT NULL)
INSERT INTO -- Parse Comma-Separate List Of Subsystems
@tblSubsystems (SubsystemID)
SELECT
CAST(SubstringValue AS INT)
FROM
Polaris.Polaris.fn_SA_GetTableOfValuesFromCommaDelimitedList(@sSubSystemList)
SELECT @nCount = COUNT(*) FROM @tblSubsystems
IF (@nCount = 1) -- Only 1 Number In List?
BEGIN
DECLARE @nSubsystemID INT
SELECT @nSubsystemID = SubsystemID FROM @tblSubsystems
IF (@nSubsystemID = 0) -- Single Value = 0 --> All Selected
BEGIN
DELETE FROM @tblSubsystems -- Clear Out 0 Row
INSERT INTO -- Collect All Subsystem IDs
@tblSubsystems (SubsystemID)
SELECT
SubsystemID
FROM
Polaris.Polaris.SA_PermissionSubsystems WITH (NOLOCK)
END
END
-- Collect Permissions Granted To User
DECLARE @tblPermissions TABLE ( CRID INT NOT NULL,
PermID INT NOT NULL,
Indirect BIT NOT NULL )
DECLARE @nLibraryID INT
-- Collect Groups That The User Is A Member Of
DECLARE @tblGroups TABLE ( GroupID INT NOT NULL )
INSERT INTO @tblGroups (GroupID)
SELECT GroupID
FROM Polaris.Polaris.GroupUsers WITH (NOLOCK)
WHERE PolarisUserID = @nUserID
UNION
SELECT gorg.GroupID
FROM Polaris.Polaris.GroupOrganizations AS gorg
WITH (NOLOCK) INNER JOIN
Polaris.Polaris.PolarisUsers AS pu
WITH (NOLOCK) ON (pu.OrganizationID = gorg.OrganizationID)
WHERE pu.PolarisUserID = @nUserID
DECLARE #PermLoop CURSOR FAST_FORWARD FOR
SELECT BranchID FROM @tblBranches
OPEN #PermLoop;
FETCH NEXT FROM #PermLoop INTO @nBranchID
WHILE (0 = @@FETCH_STATUS) -- Branch ID Cursor Fetch Loop
BEGIN
-- Retrieve Ancestor Library ID For This Branch
SELECT @nLibraryID = ParentOrganizationID
FROM Polaris.Polaris.Organizations WITH (NOLOCK)
WHERE OrganizationID = @nBranchID
-- Retrieve Directly-Granted Permissions
INSERT INTO
@tblPermissions (CRID, PermID, Indirect)
SELECT
pu.ControlRecordID, pu.PermissionID, 0
FROM
Polaris.Polaris.PermissionUsers AS pu
WITH (NOLOCK) INNER JOIN
Polaris.Polaris.ControlRecords AS cr
WITH (NOLOCK) ON (pu.ControlRecordID = cr.ControlRecordID) INNER JOIN
Polaris.Polaris.ControlRecordDefs AS crd
WITH (NOLOCK) ON (cr.ControlRecordDefID = crd.ControlRecordDefID)
WHERE
(pu.PolarisUserID = @nUserID) AND
(cr.OrganizationID IN (@nBranchID, @nLibraryID, 1)) AND
(crd.SubsystemID IN (SELECT SubsystemID FROM @tblSubsystems))
-- Retrieve Indirectly-Granted Permissions (Group Membership)
INSERT INTO
@tblPermissions (CRID, PermID, Indirect)
SELECT
pg.ControlRecordID, pg.PermissionID, 1
FROM
@tblGroups AS grp
INNER JOIN
Polaris.Polaris.PermissionGroups AS pg
WITH (NOLOCK) ON (grp.GroupID = pg.GroupID) INNER JOIN
Polaris.Polaris.ControlRecords AS cr
WITH (NOLOCK) ON (pg.ControlRecordID = cr.ControlRecordID) INNER JOIN
Polaris.Polaris.ControlRecordDefs AS crd
WITH (NOLOCK) ON (cr.ControlRecordDefID = crd.ControlRecordDefID)
WHERE
(cr.OrganizationID IN (@nBranchID, @nLibraryID, 1)) AND
(crd.SubsystemID IN (SELECT SubsystemID FROM @tblSubsystems))
FETCH NEXT FROM #PermLoop INTO @nBranchID
END;
CLOSE #PermLoop;
DEALLOCATE #PermLoop;
SELECT DISTINCT -- Return Rowset
crd.ControlRecordName AS [Control Record],
pn.PermissionName AS [Permission],
ss.[Description] AS [Subsystem],
CASE (crd.[Type])
WHEN (1) THEN (N'Not applicable')
ELSE (o.Name)
END AS [Branch/Library],
CASE (crd.[Type])
WHEN (1) THEN (N'None')
ELSE
CASE (o.OrganizationCodeID)
WHEN (3) THEN (N'Branch')
WHEN (2) THEN (N'Library')
ELSE (N'System')
END
END AS [Type]
FROM
@tblPermissions AS t
INNER JOIN
Polaris.Polaris.ControlRecords AS cr
WITH (NOLOCK) ON (cr.ControlRecordID = t.CRID) INNER JOIN
Polaris.Polaris.ControlRecordDefs AS crd
WITH (NOLOCK) ON (crd.ControlRecordDefID = cr.ControlRecordDefID) INNER JOIN
Polaris.Polaris.PermissionDefs AS pd
WITH (NOLOCK) ON (pd.PermissionID = t.PermID) INNER JOIN
Polaris.Polaris.PermissionNames AS pn
WITH (NOLOCK) ON (pn.PermissionNameID = pd.PermissionNameID) INNER JOIN
Polaris.Polaris.Organizations AS o
WITH (NOLOCK) ON (o.OrganizationID = cr.OrganizationID) INNER JOIN
Polaris.Polaris.SA_PermissionSubsystems AS ss
WITH (NOLOCK) ON (crd.SubsystemID = ss.SubsystemID)
ORDER BY
crd.ControlRecordName ASC, pn.PermissionName ASC, [Branch/Library] ASC, [Type] DESC