Produced by Araxis Merge on 5/2/2019 1:17:38 PM Central Daylight Time. See www.araxis.com for information about Merge. This report uses XHTML and CSS2, and is best viewed with a modern standards-compliant browser. For optimum results when printing this report, use landscape orientation and enable printing of background images and colours in your browser.
# | Location | File | Last Modified |
---|---|---|---|
1 | C:\AraxisMergeCompare\Pri_un\EPRS\EPRS_ODSDB\EPRS.ODSDB\EPRS.ODSDB\Programmability\Stored Procedures | uspDoAdminNotifications.sql | Thu Apr 18 16:51:44 2019 UTC |
2 | C:\AraxisMergeCompare\Pri_re\EPRS\EPRS_ODSDB\EPRS.ODSDB\EPRS.ODSDB\Programmability\Stored Procedures | uspDoAdminNotifications.sql | Tue Apr 30 12:50:34 2019 UTC |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 2 | 270 |
Changed | 1 | 2 |
Inserted | 0 | 0 |
Removed | 0 | 0 |
Whitespace | |
---|---|
Character case | Differences in character case are significant |
Line endings | Differences in line endings (CR and LF characters) are ignored |
CR/LF characters | Not shown in the comparison detail |
No regular expressions were active.
1 | -- ======= ========== ========== ========== ======== | |
2 | -- Descrip tion: Noti fies admin istrators by email o f user acc ounts that have been added/cha nged | |
3 | -- | |
4 | -- Mainten ance Log: | |
5 | -- | |
6 | -- Update By Upd ate Date Descrip tion | |
7 | -- ------- - --- ------ --------- ---------- --------- | |
8 | ||
9 | -- ======= ========== ========== ========== ======== | |
10 | CREATE PRO CEDURE [db o].[uspDoA dminNotifi cations] | |
11 | @d ays int | |
12 | ||
13 | AS | |
14 | DE CLARE @ACC ESS_STATUS varchar(2 5), | |
15 | @USER_ ROLE varch ar(25), | |
16 | @TABLE _DATA NVAR CHAR(max), | |
17 | @BODY_ DATA NVARC HAR(MAX), | |
18 | @useri d int, | |
19 | @email varchar(1 00), | |
20 | @FROM_ DATE datet ime2, | |
21 | @CHECK _DATE date time2, | |
22 | @COUNT int | |
23 | ||
24 | DE CLARE @res ultsTable table | |
25 | ( | |
26 | User RoleID int , | |
27 | Name varchar(5 0), | |
28 | Emai lAddress v archar(50) , | |
29 | Supv EmailAddre ss varchar (50), | |
30 | Role Name varch ar(25), | |
31 | Acce ssLevelNam e varchar( 25), | |
32 | Date Created Da teTime2, | |
33 | Acce ssStatus v archar(25) | |
34 | ) | |
35 | ||
36 | DE CLARE @ema ilList tab le | |
37 | ( | |
38 | User ID int, | |
39 | emai lAddress v archar(50) , | |
40 | Name varchar(5 0), | |
41 | Role Name varch ar(25) | |
42 | ) | |
43 | ||
44 | BEGIN | |
45 | SE T NOCOUNT ON; | |
46 | ||
47 | BE GIN TRY | |
48 | BEGI N | |
49 | SET @C OUNT = 0 | |
50 | SET @A CCESS_STAT US = 'REQU ESTED' | |
51 | SET @U SER_ROLE = 'ADMINIST RATOR' | |
52 | SET @F ROM_DATE = DATEADD(D AY, -@days , GETDATE( )) | |
53 | SET @ CHECK_DATE = DATEADD( MINUTE, -1 , @FROM_DA TE) | |
54 | ||
55 | if @da ys = null SET @days = 7 | |
56 | ||
57 | INSERT INTO @res ultsTable (UserRoleI D, Name, E mailAddres s, SupvEma ilAddress, RoleName, AccessLev elName, Da teCreated, AccessSta tus) | |
58 | SELECT seclyr.Us erRoles.Us erRoleID, seclyr.Use rs.LastNam e + ', ' + seclyr.Us ers.FirstN ame AS Nam e, seclyr. Users.Emai lAddress, seclyr.Use rs.Supervi sorEmailAd dress, sec lyr.Roles. RoleName, seclyr.Acc essLevels. AccessLeve lName, sec lyr.UserRo les.DateCr eated, sec lyr.UserRo les.Access Status | |
59 | FROM s eclyr.User Roles INNE R JOIN | |
60 | seclyr. Users ON s eclyr.User Roles.User ID = secly r.Users.Us erID INNER JOIN | |
61 | seclyr. Roles ON s eclyr.User Roles.Role ID = secly r.Roles.Ro leID INNER JOIN | |
62 | seclyr. AccessLeve ls ON secl yr.UserRol es.AccessL evelID = s eclyr.Acce ssLevels.A ccessLevel ID | |
63 | WHERE UPPER(se clyr.UserR oles.Acces sStatus) = @ACCESS_S TATUS | |
64 | AND secl yr.UserRol es.DateCre ated >= @C HECK_DATE | |
65 | ||
66 | END | |
67 | ||
68 | BEGI N | |
69 | INSERT INTO @ema ilList (Us erID, emai lAddress, Name, Role Name) | |
70 | SELECT DISTINC T seclyr. Users.User ID, seclyr .Users.Ema ilAddress, seclyr.Us ers.LastNa me + ', ' +seclyr.Us ers.FirstN ame AS Nam e, seclyr. Roles.Role Name | |
71 | FROM s eclyr.Role s INNER JO IN | |
72 | sec lyr.UserRo les ON sec lyr.Roles. RoleID = s eclyr.User Roles.Role ID INNER J OIN | |
73 | sec lyr.Users ON seclyr. UserRoles. UserID = s eclyr.User s.UserID | |
74 | WHERE UPPER(secl yr.Roles.R oleName) = @USER_ROL E | |
75 | AND UP PER(seclyr .UserRoles .AccessSta tus) = 'Ap proved' | |
76 | END | |
77 | ||
78 | SET @COUNT = ( SELECT COU NT(DISTINC T UserRole ID) | |
79 | FROM @resultsT able) | |
80 | ||
81 | IF @ COUNT > 0 | |
82 | BEGI N | |
83 | SET @T ABLE_DATA = CAST((SE LECT [Name ] AS 'td', '', [Rol eName] AS 'td', '', [AccessLe velName] A S 'td', '' , [Access Status] AS 'td', '', CONVERT(v archar(18) , [DateCre ated], 0) AS 'td' | |
84 | FROM @ resultsTab le FOR XML PATH('tr' ), ELEMENT S ) AS NVA RCHAR(MAX) ) | |
85 | ||
86 | SET @B ODY_DATA = '<html><b ody><h3>Ne w user acc ount actio ns that ne ed process ing</h3> | |
87 | <p><b>Re quests ent ered since ' + CONVE RT(varchar (12), @CHE CK_DATE, 0 ) + | |
88 | '</p><ta ble border = 1> | |
89 | <tr> | |
90 | <th>Name </th><th>R ole</th><t h>Access L evel</th>< th>Status< /th><th>Re quest Date </th></tr> ' | |
91 | ||
92 | SET @B ODY_DATA = @BODY_DAT A + @TABLE _DATA + '< /table> | |
93 | <p>Thi s message was genera ted from a n automate d server. <u>Do not reply</u> to this m essage.</b ></p> | |
94 | </body ></html>' | |
95 | ||
96 | DECLAR E userid C URSOR FOR | |
97 | SELECT userid FR OM @emailL ist | |
98 | ||
99 | OPEN u serid | |
100 | ||
101 | set @u serid = 0 | |
102 | set @e mail = '' | |
103 | ||
104 | FETCH FROM useri d into @us erid | |
105 | WHILE @@FETCH_ST ATUS = 0 | |
106 | BEGIN | |
107 | SET @ema il = (Sele ct top 1 e mailAddres s from @em ailList wh ere UserID = @userid ) | |
108 | ||
109 | FETCH NE XT FROM us erid into @userid | |
110 | ||
111 | EXEC msd b.dbo.sp_s end_dbmail @profile_ name='SQL Admins', | |
112 | @recipie nts=@email , | |
113 | --@copy_re cipients = 'steve.ko pecky@ DOMAIN . EXT ;kenneth.b aker2@ DOMAIN . EXT ;joshua.da ncy@ DOMAIN . EXT ', | |
114 | @subject ='EPRS Adm inistrator Notificat ions', | |
115 | @body=@B ODY_DATA, | |
116 | @body_fo rmat = 'HT ML' | |
117 | END | |
118 | CLOSE userid | |
119 | DEALLO CATE useri d | |
120 | RETURN ( @COUNT ) | |
121 | END | |
122 | ELSE | |
123 | BEGI N | |
124 | RETURN (0) | |
125 | END | |
126 | EN D TRY | |
127 | ||
128 | ||
129 | BE GIN CATCH | |
130 | PRIN T 'There w as an erro r sending admin noti fication' + CHAR(13) ; | |
131 | SELE CT * FROM dbo.ufnGet ErrorInfo( ); | |
132 | EXEC uspRaiseE rrorInfo; | |
133 | EN D CATCH | |
134 | ||
135 | END | |
136 |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.