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: Returns user details.
-- =============================================
CREATE PROCEDURE [seclyr].[uspRptDetUserMgt]
@moduleIds varchar(max),
@adDomains varchar(max),
@accessLevelIds varchar(max),
@sensitivityLevelIds varchar(max),
@accessStatuses varchar(max),
@userId int,
@partialName varchar(max)
AS
DECLARE @stationsTable TABLE
(
RowID int,
ModuleID tinyint,
RegionID tinyint, --this is VHA Region
CCNRegionID tinyint, --this is CCN Region
VisnID tinyint,
StationID smallint,
ParentStationID smallint,
Station3N varchar(10),
Station6A varchar(10),
StationDisplayName varchar(100),
StationName varchar(100),
IsParent bit,
Inactive bit
)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--IF @partialName is NULL, then set to everything wildcard %
IF @partialName IS NULL
SET @partialName = '%';
--populate what stations the user has permission to see
INSERT INTO @stationsTable (RowID, ModuleID, RegionID, CCNRegionID, VisnID, StationID, ParentStationID, Station3N, Station6A
, StationDisplayName, StationName, IsParent, Inactive)
EXEC seclyr.uspGetUserParentStations @userId, @moduleIds;
SELECT DISTINCT
ADDomain
, LastName
, FirstName
, RoleName
, AccessLevelName
, seclyr.UserRoles.ModuleID
, ModuleName
, seclyr.UserRoles.RegionID
, AssignedRegions.RegionCode
, seclyr.UserRoles.CCNRegionID
, AssignedCCNRegions.RegionCode AS 'CCNRegionCode'
, seclyr.UserRoles.VisnID
, AssignedVisns.VisnCode
, seclyr.UserRoles.StationID
, Stations.Station3N
, AccessStatus
, PointOfContact
, seclyr.UserRoles.SensitivityLevelID
, SensitivityLevelName
, Comment
, UserName
, EmailAddress
, DateLastLogin
, seclyr.Users.DateInactive
, seclyr.Users.Inactive
FROM seclyr.UserRoles
INNER JOIN seclyr.Users ON seclyr.Users.UserID = seclyr.UserRoles.UserID
INNER JOIN seclyr.Roles ON seclyr.Roles.RoleID = seclyr.UserRoles.RoleID
INNER JOIN seclyr.AccessLevels ON seclyr.AccessLevels.AccessLevelID = seclyr.UserRoles.AccessLevelID
INNER JOIN seclyr.Modules ON seclyr.Modules.ModuleID = seclyr.UserRoles.ModuleID
INNER JOIN seclyr.SensitivityLevels ON seclyr.SensitivityLevels.SensitivityLevelID = seclyr.UserRoles.SensitivityLevelID
LEFT OUTER JOIN @stationsTable Stations ON seclyr.UserRoles.StationID = Stations.StationID
LEFT OUTER JOIN Visns AssignedVisns ON seclyr.UserRoles.VisnID = AssignedVisns.VisnID
LEFT OUTER JOIN Regions AssignedRegions ON seclyr.UserRoles.RegionID = AssignedRegions.RegionID
LEFT OUTER JOIN Regions AssignedCCNRegions ON seclyr.UserRoles.CCNRegionID = AssignedRegions.RegionID
WHERE
(ADDomain IN(SELECT [Value] FROM ufnSplit(',', @adDomains)) or @adDomains IS NULL)
AND (seclyr.UserRoles.ModuleID IN(SELECT [Value] FROM ufnSplit(',', @moduleIds)) or @moduleIds IS NULL)
AND (seclyr.UserRoles.AccessLevelID IN(SELECT [Value] FROM ufnSplit(',', @accessLevelIds)) OR @accessLevelIds IS NULL)
AND (seclyr.UserRoles.SensitivityLevelID IN(SELECT [Value] FROM ufnSplit(',', @sensitivityLevelIds)) OR @sensitivityLevelIds IS NULL)
AND (ISNULL(seclyr.UserRoles.AccessStatus, NULL) IN(SELECT [Value] FROM ufnSplit(',', @accessStatuses)) or @accessStatuses IS NULL)
AND (LastName + ', ' + FirstName) like '%'+ @partialName + '%'
ORDER BY ADDomain, seclyr.UserRoles.VisnID, seclyr.UserRoles.StationID, LastName, FirstName
;
END TRY
BEGIN CATCH
PRINT 'There was an error processing the User Management Details' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
--DECLARE @webMessage varchar(400)
--SET @webMessage= 'AD Domains: ' +
-- CASE WHEN LEN(@ADDomains) > 0 THEN @ADDomains ELSE 'None specified' END
-- + ', Region Ids: :' +
-- CASE WHEN LEN(@regionIds) > 0 THEN @regionIds ELSE 'None specified' END;
--EXEC uspSaveRptParameters @userId, 'uspRptUserRoles', @webMessage;
END