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