Summary Table

Categories Total Count
PII 3
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: Notifies administrators by email of user accounts that have been added/changed
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- -------- --------- ----------------------------

-- =============================================
CREATE PROCEDURE [dbo].[uspDoAdminNotifications]
@days int

AS
DECLARE @ACCESS_STATUS varchar(25),
@USER_ROLE varchar(25),
@TABLE_DATA NVARCHAR(max),
@BODY_DATA NVARCHAR(MAX),
@userid int,
@email varchar(100),
@FROM_DATE datetime2,
@CHECK_DATE datetime2,
@COUNT int

DECLARE @resultsTable table
(
UserRoleID int,
Name varchar(50),
EmailAddress varchar(50),
SupvEmailAddress varchar(50),
RoleName varchar(25),
AccessLevelName varchar(25),
DateCreated DateTime2,
AccessStatus varchar(25)
)

DECLARE @emailList table
(
UserID int,
emailAddress varchar(50),
Name varchar(50),
RoleName varchar(25)
)

BEGIN
SET NOCOUNT ON;

BEGIN TRY
BEGIN
SET @COUNT = 0
SET @ACCESS_STATUS = 'REQUESTED'
SET @USER_ROLE = 'ADMINISTRATOR'
SET @FROM_DATE = DATEADD(DAY, -@days, GETDATE())
SET @CHECK_DATE= DATEADD(MINUTE, -1, @FROM_DATE)

if @days = null SET @days = 7

INSERT INTO @resultsTable (UserRoleID, Name, EmailAddress, SupvEmailAddress, RoleName, AccessLevelName, DateCreated, AccessStatus)
SELECT seclyr.UserRoles.UserRoleID, seclyr.Users.LastName + ', ' + seclyr.Users.FirstName AS Name, seclyr.Users.EmailAddress, seclyr.Users.SupervisorEmailAddress, seclyr.Roles.RoleName, seclyr.AccessLevels.AccessLevelName, seclyr.UserRoles.DateCreated, seclyr.UserRoles.AccessStatus
FROM seclyr.UserRoles INNER JOIN
seclyr.Users ON seclyr.UserRoles.UserID = seclyr.Users.UserID INNER JOIN
seclyr.Roles ON seclyr.UserRoles.RoleID = seclyr.Roles.RoleID INNER JOIN
seclyr.AccessLevels ON seclyr.UserRoles.AccessLevelID = seclyr.AccessLevels.AccessLevelID
WHERE UPPER(seclyr.UserRoles.AccessStatus) = @ACCESS_STATUS
AND seclyr.UserRoles.DateCreated >= @CHECK_DATE

END

BEGIN
INSERT INTO @emailList (UserID, emailAddress, Name, RoleName)
SELECT DISTINCT seclyr.Users.UserID, seclyr.Users.EmailAddress, seclyr.Users.LastName + ', ' +seclyr.Users.FirstName AS Name, seclyr.Roles.RoleName
FROM seclyr.Roles INNER JOIN
seclyr.UserRoles ON seclyr.Roles.RoleID = seclyr.UserRoles.RoleID INNER JOIN
seclyr.Users ON seclyr.UserRoles.UserID = seclyr.Users.UserID
WHERE UPPER(seclyr.Roles.RoleName) = @USER_ROLE
AND UPPER(seclyr.UserRoles.AccessStatus) = 'Approved'
END

SET @COUNT = (SELECT COUNT(DISTINCT UserRoleID)
FROM @resultsTable)

IF @COUNT > 0
BEGIN
SET @TABLE_DATA = CAST((SELECT [Name] AS 'td', '', [RoleName] AS 'td', '', [AccessLevelName] AS 'td', '', [AccessStatus] AS 'td', '', CONVERT(varchar(18), [DateCreated], 0) AS 'td'
FROM @resultsTable FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @BODY_DATA = '<html><body><h3>New user account actions that need processing</h3>
<p><b>Requests entered since ' + CONVERT(varchar(12), @CHECK_DATE, 0) +
'</p><table border = 1>
<tr>
<th>Name</th><th>Role</th><th>Access Level</th><th>Status</th><th>Request Date</th></tr>'

SET @BODY_DATA = @BODY_DATA + @TABLE_DATA + '</table>
<p>This message was generated from an automated server. <u>Do not reply</u> to this message.</b></p>
</body></html>'

DECLARE userid CURSOR FOR
SELECT userid FROM @emailList

OPEN userid

set @userid = 0
set @email = ''

FETCH FROM userid into @userid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @email = (Select top 1 emailAddress from @emailList where UserID = @userid)

FETCH NEXT FROM userid into @userid

EXEC msdb.dbo.sp_send_dbmail @profile_name='SQL Admins',
@recipients=@email,
--@copy_recipients = '
PII;PII;PII ',
@subject='EPRS Administrator Notifications',
@body=@BODY_DATA,
@body_format = 'HTML'
END
CLOSE userid
DEALLOCATE userid
RETURN ( @COUNT )
END
ELSE
BEGIN
RETURN (0)
END
END TRY


BEGIN CATCH
PRINT 'There was an error sending admin notification' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH

END