Summary Table

Categories Total Count
PII 3
URL 1
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 the number of user accounts that have been added/changed
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- -------- --------- ----------------------------

-- =============================================
CREATE PROCEDURE [dbo].[uspDoAdminNotificationsCount]

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

DECLARE @resultsTable table
(
UserRoleID int,
DateCreated Datetime2
)

DECLARE @emailList table
(
UserID int,
emailAddress varchar(50),
Name varchar(50),
RoleName varchar(25)
)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
BEGIN
SET @COUNT = 0
SET @ACCESS_STATUS = 'REQUESTED'
SET @USER_ROLE = 'ADMINISTRATOR'

INSERT INTO @resultsTable (UserRoleID, DateCreated)
SELECT seclyr.UserRoles.UserRoleID, seclyr.UserRoles.DateCreated
FROM seclyr.UserRoles
WHERE UPPER(seclyr.UserRoles.AccessStatus) = @ACCESS_STATUS

SET @COUNT = (SELECT COUNT(DISTINCT UserRoleID)
FROM @resultsTable)
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

IF @COUNT > 0
BEGIN
SET @BODY_DATA = '<html><body><h3>Enterprise Program Reporting System actions</h3><h4>There are currently ' +
FORMAT(@COUNT, '#,0')+
' pending user account actions that require attention.</h4>
<p>Run Date: ' +
CONVERT(varchar(18), GETDATE(), 100) +
'<p>You can access these actions here: <a href="https://
URL /EPRSDev/Administration">User Worklist</a></p>
<p><br />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 counts' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH

END
GO