Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =============================================
-- Description: Identifies the sensitivity level for a user / CCN Region.
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [seclyr].[uspRptGetUserCCNRegionMaxSensitivityLevel] (
@userId int,
@moduleIds varchar(max),
@ccnRegionIds varchar(max)
)
AS
BEGIN
DECLARE @maxSensitivityLevelID tinyint
DECLARE @userRoleTable TABLE
(
ID int,
UserID int,
ModuleID smallint,
RegionID tinyint, --this is VHA Region
CCNRegionID tinyint, --this is CCN Region
VisnID tinyint,
StationID smallint,
StationName varchar(250),
RoleID tinyint,
AccessLevelID tinyint,
AccessStatus varchar(50),
SensitivityLevelID tinyint,
SensitivityLevelNamme varchar(100),
CanEdit bit
)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--If @ccnRegionIds should use zero (null CCN Region placeholder),
--null that out so can get a proper answer
IF @ccnRegionIds = '0'
BEGIN
SET @ccnRegionIds = Null;
END
INSERT INTO @userRoleTable (ID, UserID, ModuleID, RegionID, CCNRegionID, VisnID, StationID, RoleID,
AccessStatus, AccessLevelID, SensitivityLevelID, CanEdit)
EXEC seclyr.uspGetUserStationRoles @userId, @moduleIds;
SELECT DISTINCT
ModuleID
, CCNRegionID
, MAX(SensitivityLevelID) AS MaxSensitivityLevelID
FROM @userRoleTable
WHERE (CCNRegionID In(SELECT [Value] FROM ufnSplit(',', @ccnRegionIds)) OR @ccnRegionIds IS NULL)
AND (ModuleID In(SELECT [Value] FROM ufnSplit(',', @moduleIds)) OR @moduleIds IS NULL)
AND AccessStatus = 'APPROVED'
GROUP BY ModuleID, CCNRegionID;
END TRY
BEGIN CATCH
PRINT 'There was an error getting maximum sensitivity level for user/CCN Region reporting' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END
GO