· 7 years ago · Sep 07, 2018, 03:34 PM
1USE [master]
2GO
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7IF NOT EXISTS (SELECT * FROM [master].[dbo].sysobjects WHERE xtype = 'U' AND name = 'Trace_Location')
8BEGIN
9 create table [master].[dbo].[Trace_Location] (
10 TraceId INT IDENTITY(1,1) PRIMARY KEY,
11 TracePath varchar(200)
12 )ON [PRIMARY]
13END
14GO
15
16declare @Edition varchar(255)
17select @Edition = CAST(ServerProperty('Edition') AS nvarchar(100))
18
19if(@Edition not like '%Express%')
20begin
21 exec('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[NBC_Trace]'') AND type in (N''P'', N''PC''))
22 DROP PROCEDURE [dbo].[NBC_Trace]
23
24 SET ANSI_NULLS ON
25 SET QUOTED_IDENTIFIER OFF')
26
27 exec('create procedure [dbo].[NBC_Trace]
28 as
29 begin
30 --Event ID with explanation
31 -- 14 -- Audit Login
32 -- 15 -- Audit Logout
33 -- 18 -- Audit Server Starts and Stops
34 -- 20 -- Audit Login Failed
35 -- 102 -- Audit Database Scope GDR - Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server for database-only actions such as granting permissions on a database
36 -- 103 -- Audit Object GDR Event - Occurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server
37 -- 104 -- Audit AddLogin Event - Occurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin
38 -- 105 -- Audit Login GDR Event - Occurs when a Windows login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin
39 -- 106 -- Audit Login Change Property Event - Occurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage
40 -- 107 -- Audit Login Change Password Event - Occurs when a SQL Server login password is changed
41 -- 108 -- Audit Add Login to Server Role Event - Occurs when a login is added or removed from a fixed server role; for sp_addsrvrolemember, and sp_dropsrvrolemember
42 -- 109 -- Audit Add DB User Event - Occurs when a login is added or removed as a database user (Windows or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess, sp_adduser, and sp_dropuser
43 -- 110 -- Audit Add Member to DB Role Event - Occurs when a login is added or removed as a database user (fixed or user-defined) to a database; for sp_addrolemember, sp_droprolemember, and sp_changegroup
44 -- 111 -- Audit Add Role Event - Occurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole
45 -- 112 -- Audit App Role Change Password Event - Occurs when a password of an application role is changed
46 -- 113 -- Audit Statement Permission Event - Occurs when a statement permission (such as CREATE TABLE) is used
47 -- 115 -- Audit Backup/Restore Event - Occurs when a BACKUP or RESTORE command is issued
48 -- 116 -- Audit DBCC Event - Occurs when DBCC commands are issued
49 -- 117 -- Audit Change Audit Event - Occurs when audit trace modifications are made
50 -- 128 -- Audit Database Management Event - Occurs when a database is created, altered, or dropped
51 -- 129 -- Audit Database Object Management Event - Occurs when a CREATE, ALTER, or DROP statement executes on database objects, such as schemas
52 -- 130 -- Audit Database Principal Management Event - Occurs when principals, such as users, are created, altered, or dropped from a database
53 -- 131 -- Audit Schema Object Management Event - Occurs when server objects are created, altered, or dropped
54 -- 132 -- Audit Server Principal Impersonation Event - Occurs when there is an impersonation within server scope, such as EXECUTE AS LOGIN
55 -- 134 -- Audit Server Object Take Ownership Event - Occurs when the owner is changed for objects in server scope
56 -- 135 -- Audit Database Object Take Ownership Event - Occurs when a change of owner for objects within database scope occurs
57 -- 152 -- Audit Change Database Owner - Occurs when ALTER AUTHORIZATION is used to change the owner of a database and permissions are checked to do that
58 -- 153 -- Audit Schema Object Take Ownership Event - Occurs when ALTER AUTHORIZATION is used to assign an owner to an object and permissions are checked to do that
59 -- 170 -- Audit Server Scope GDR Event - Indicates that a grant, deny, or revoke event for permissions in server scope occurred, such as creating a login
60 -- 171 -- Audit Server Object GDR Event - Indicates that a grant, deny, or revoke event for a schema object, such as a table or function, occurred
61 -- 172 -- Audit Database Object GDR Event - Indicates that a grant, deny, or revoke event for database objects, such as assemblies and schemas, occurred
62 -- 175 -- Audit Server Alter Trace Event - Occurs when a statement checks for the ALTER TRACE permission
63 -- 176 -- Audit Server Object Management Event - Occurs when server objects are created, altered, or dropped - https://technet.microsoft.com/en-us/library/ms190382.aspx - could be removed?
64 -- 177 -- Audit Server Principal Management Event - Occurs when server principals are created, altered, or dropped - Logins/Roles
65
66 declare @nbcTrace nvarchar(20) = ''NBC_Trace''
67 declare @rc varchar(200)
68 declare @id int = 0
69
70 select @id = id from sys.traces where path like ''%'' + @nbcTrace + ''%''
71
72 if(@id > 0)
73 begin
74 exec sp_trace_setstatus @id,0
75 exec sp_trace_setstatus @id,2
76 end
77
78 -- Create a Queue
79 declare @TraceID int = 0
80 declare @maxfilesize bigint
81 declare @dir nvarchar(4000) = null
82
83 set @maxfilesize = 10
84
85 select top 1 @dir = TracePath from [master].[dbo].[Trace_Location]
86 -- exec prism.dbo.SP_GetBackupDirectory @dir output
87
88 if(@dir is not null)
89 begin
90
91 set @dir = @dir + @nbcTrace + ''_'' + CONVERT(VARCHAR(10),GetDate(),110) + ''_'' + convert(varchar(12), datepart(hour,GetDate())) + convert(varchar(12), datepart(minute,GetDate())) + convert(varchar(12), datepart(second,GetDate())) + ''NNN''
92
93 -------BEGIN THIS WILL RENAME CURRENT EXISTING .trc to _1.trc-------------
94 declare @i int
95 declare @test varchar(250) = @dir + ''.trc''
96 exec master..xp_fileexist @test, @i output
97
98 if @i = 1
99 begin
100 declare @move varchar(200) = ''move "'' + @test + ''" "'' + substring(@test,0,len(@dir)-3) + ''_1.trc"''
101
102 exec master..xp_cmdshell @move
103 end
104 -------END RENAME----------------------------------------
105
106 exec @rc = sp_trace_create @TraceID output, 2, @dir , @maxfilesize, NULL
107 if (@rc != 0)
108 begin
109 insert into prism.dbo.systemlog (apptext,moduleid) values(''SST: Unable to create trace.'',775)
110 return 0
111 end
112
113 -- Client side File and Table cannot be scripted
114
115 -- Set the events
116 declare @on bit
117 set @on = 1
118 exec sp_trace_setevent @TraceID, 109, 11, @on
119 exec sp_trace_setevent @TraceID, 109, 12, @on
120 exec sp_trace_setevent @TraceID, 109, 6, @on
121 exec sp_trace_setevent @TraceID, 109, 9, @on
122 exec sp_trace_setevent @TraceID, 109, 10, @on
123 exec sp_trace_setevent @TraceID, 109, 14, @on
124 exec sp_trace_setevent @TraceID, 109, 8, @on
125 exec sp_trace_setevent @TraceID, 109, 26, @on
126 exec sp_trace_setevent @TraceID, 108, 1, @on
127 exec sp_trace_setevent @TraceID, 108, 9, @on
128 exec sp_trace_setevent @TraceID, 108, 11, @on
129 exec sp_trace_setevent @TraceID, 108, 6, @on
130 exec sp_trace_setevent @TraceID, 108, 10, @on
131 exec sp_trace_setevent @TraceID, 108, 12, @on
132 exec sp_trace_setevent @TraceID, 108, 14, @on
133 exec sp_trace_setevent @TraceID, 108, 8, @on
134 exec sp_trace_setevent @TraceID, 108, 26, @on
135 exec sp_trace_setevent @TraceID, 110, 1, @on
136 exec sp_trace_setevent @TraceID, 110, 9, @on
137 exec sp_trace_setevent @TraceID, 110, 11, @on
138 exec sp_trace_setevent @TraceID, 110, 6, @on
139 exec sp_trace_setevent @TraceID, 110, 10, @on
140 exec sp_trace_setevent @TraceID, 110, 12, @on
141 exec sp_trace_setevent @TraceID, 110, 14, @on
142 exec sp_trace_setevent @TraceID, 110, 8, @on
143 exec sp_trace_setevent @TraceID, 110, 26, @on
144 exec sp_trace_setevent @TraceID, 111, 11, @on
145 exec sp_trace_setevent @TraceID, 111, 12, @on
146 exec sp_trace_setevent @TraceID, 111, 6, @on
147 exec sp_trace_setevent @TraceID, 111, 9, @on
148 exec sp_trace_setevent @TraceID, 111, 10, @on
149 exec sp_trace_setevent @TraceID, 111, 14, @on
150 exec sp_trace_setevent @TraceID, 111, 8, @on
151 exec sp_trace_setevent @TraceID, 111, 26, @on
152 exec sp_trace_setevent @TraceID, 104, 11, @on
153 exec sp_trace_setevent @TraceID, 104, 6, @on
154 exec sp_trace_setevent @TraceID, 104, 9, @on
155 exec sp_trace_setevent @TraceID, 104, 10, @on
156 exec sp_trace_setevent @TraceID, 104, 12, @on
157 exec sp_trace_setevent @TraceID, 104, 14, @on
158 exec sp_trace_setevent @TraceID, 104, 8, @on
159 exec sp_trace_setevent @TraceID, 104, 26, @on
160 exec sp_trace_setevent @TraceID, 112, 1, @on
161 exec sp_trace_setevent @TraceID, 112, 9, @on
162 exec sp_trace_setevent @TraceID, 112, 11, @on
163 exec sp_trace_setevent @TraceID, 112, 6, @on
164 exec sp_trace_setevent @TraceID, 112, 10, @on
165 exec sp_trace_setevent @TraceID, 112, 12, @on
166 exec sp_trace_setevent @TraceID, 112, 14, @on
167 exec sp_trace_setevent @TraceID, 112, 8, @on
168 exec sp_trace_setevent @TraceID, 112, 26, @on
169 exec sp_trace_setevent @TraceID, 115, 1, @on
170 exec sp_trace_setevent @TraceID, 115, 9, @on
171 exec sp_trace_setevent @TraceID, 115, 6, @on
172 exec sp_trace_setevent @TraceID, 115, 10, @on
173 exec sp_trace_setevent @TraceID, 115, 11, @on
174 exec sp_trace_setevent @TraceID, 115, 12, @on
175 exec sp_trace_setevent @TraceID, 115, 14, @on
176 exec sp_trace_setevent @TraceID, 115, 8, @on
177 exec sp_trace_setevent @TraceID, 115, 26, @on
178 exec sp_trace_setevent @TraceID, 117, 1, @on
179 exec sp_trace_setevent @TraceID, 117, 9, @on
180 exec sp_trace_setevent @TraceID, 117, 11, @on
181 exec sp_trace_setevent @TraceID, 117, 6, @on
182 exec sp_trace_setevent @TraceID, 117, 10, @on
183 exec sp_trace_setevent @TraceID, 117, 12, @on
184 exec sp_trace_setevent @TraceID, 117, 14, @on
185 exec sp_trace_setevent @TraceID, 117, 8, @on
186 exec sp_trace_setevent @TraceID, 117, 26, @on
187 exec sp_trace_setevent @TraceID, 152, 1, @on
188 exec sp_trace_setevent @TraceID, 152, 9, @on
189 exec sp_trace_setevent @TraceID, 152, 11, @on
190 exec sp_trace_setevent @TraceID, 152, 6, @on
191 exec sp_trace_setevent @TraceID, 152, 10, @on
192 exec sp_trace_setevent @TraceID, 152, 12, @on
193 exec sp_trace_setevent @TraceID, 152, 14, @on
194 exec sp_trace_setevent @TraceID, 152, 8, @on
195 exec sp_trace_setevent @TraceID, 152, 26, @on
196 exec sp_trace_setevent @TraceID, 116, 1, @on
197 exec sp_trace_setevent @TraceID, 116, 9, @on
198 exec sp_trace_setevent @TraceID, 116, 6, @on
199 exec sp_trace_setevent @TraceID, 116, 10, @on
200 exec sp_trace_setevent @TraceID, 116, 11, @on
201 exec sp_trace_setevent @TraceID, 116, 12, @on
202 exec sp_trace_setevent @TraceID, 116, 14, @on
203 exec sp_trace_setevent @TraceID, 116, 8, @on
204 exec sp_trace_setevent @TraceID, 116, 26, @on
205 exec sp_trace_setevent @TraceID, 128, 1, @on
206 exec sp_trace_setevent @TraceID, 128, 11, @on
207 exec sp_trace_setevent @TraceID, 128, 12, @on
208 exec sp_trace_setevent @TraceID, 128, 6, @on
209 exec sp_trace_setevent @TraceID, 128, 10, @on
210 exec sp_trace_setevent @TraceID, 128, 14, @on
211 exec sp_trace_setevent @TraceID, 128, 8, @on
212 exec sp_trace_setevent @TraceID, 128, 26, @on
213 exec sp_trace_setevent @TraceID, 172, 1, @on
214 exec sp_trace_setevent @TraceID, 172, 9, @on
215 exec sp_trace_setevent @TraceID, 172, 2, @on
216 exec sp_trace_setevent @TraceID, 172, 10, @on
217 exec sp_trace_setevent @TraceID, 172, 6, @on
218 exec sp_trace_setevent @TraceID, 172, 11, @on
219 exec sp_trace_setevent @TraceID, 172, 12, @on
220 exec sp_trace_setevent @TraceID, 172, 14, @on
221 exec sp_trace_setevent @TraceID, 172, 8, @on
222 exec sp_trace_setevent @TraceID, 172, 26, @on
223 exec sp_trace_setevent @TraceID, 129, 1, @on
224 exec sp_trace_setevent @TraceID, 129, 11, @on
225 exec sp_trace_setevent @TraceID, 129, 6, @on
226 exec sp_trace_setevent @TraceID, 129, 10, @on
227 exec sp_trace_setevent @TraceID, 129, 12, @on
228 exec sp_trace_setevent @TraceID, 129, 14, @on
229 exec sp_trace_setevent @TraceID, 129, 8, @on
230 exec sp_trace_setevent @TraceID, 129, 26, @on
231 exec sp_trace_setevent @TraceID, 135, 1, @on
232 exec sp_trace_setevent @TraceID, 135, 11, @on
233 exec sp_trace_setevent @TraceID, 135, 12, @on
234 exec sp_trace_setevent @TraceID, 135, 6, @on
235 exec sp_trace_setevent @TraceID, 135, 10, @on
236 exec sp_trace_setevent @TraceID, 135, 14, @on
237 exec sp_trace_setevent @TraceID, 135, 8, @on
238 exec sp_trace_setevent @TraceID, 135, 26, @on
239 exec sp_trace_setevent @TraceID, 130, 1, @on
240 exec sp_trace_setevent @TraceID, 130, 11, @on
241 exec sp_trace_setevent @TraceID, 130, 12, @on
242 exec sp_trace_setevent @TraceID, 130, 6, @on
243 exec sp_trace_setevent @TraceID, 130, 10, @on
244 exec sp_trace_setevent @TraceID, 130, 14, @on
245 exec sp_trace_setevent @TraceID, 130, 8, @on
246 exec sp_trace_setevent @TraceID, 130, 26, @on
247 exec sp_trace_setevent @TraceID, 102, 1, @on
248 exec sp_trace_setevent @TraceID, 102, 9, @on
249 exec sp_trace_setevent @TraceID, 102, 2, @on
250 exec sp_trace_setevent @TraceID, 102, 6, @on
251 exec sp_trace_setevent @TraceID, 102, 10, @on
252 exec sp_trace_setevent @TraceID, 102, 11, @on
253 exec sp_trace_setevent @TraceID, 102, 12, @on
254 exec sp_trace_setevent @TraceID, 102, 14, @on
255 exec sp_trace_setevent @TraceID, 102, 8, @on
256 exec sp_trace_setevent @TraceID, 102, 26, @on
257 exec sp_trace_setevent @TraceID, 14, 1, @on
258 exec sp_trace_setevent @TraceID, 14, 9, @on
259 exec sp_trace_setevent @TraceID, 14, 2, @on
260 exec sp_trace_setevent @TraceID, 14, 10, @on
261 exec sp_trace_setevent @TraceID, 14, 11, @on
262 exec sp_trace_setevent @TraceID, 14, 6, @on
263 exec sp_trace_setevent @TraceID, 14, 12, @on
264 exec sp_trace_setevent @TraceID, 14, 14, @on
265 exec sp_trace_setevent @TraceID, 14, 8, @on
266 exec sp_trace_setevent @TraceID, 14, 26, @on
267 exec sp_trace_setevent @TraceID, 107, 1, @on
268 exec sp_trace_setevent @TraceID, 107, 9, @on
269 exec sp_trace_setevent @TraceID, 107, 11, @on
270 exec sp_trace_setevent @TraceID, 107, 6, @on
271 exec sp_trace_setevent @TraceID, 107, 10, @on
272 exec sp_trace_setevent @TraceID, 107, 12, @on
273 exec sp_trace_setevent @TraceID, 107, 14, @on
274 exec sp_trace_setevent @TraceID, 107, 8, @on
275 exec sp_trace_setevent @TraceID, 107, 26, @on
276 exec sp_trace_setevent @TraceID, 106, 1, @on
277 exec sp_trace_setevent @TraceID, 106, 9, @on
278 exec sp_trace_setevent @TraceID, 106, 6, @on
279 exec sp_trace_setevent @TraceID, 106, 10, @on
280 exec sp_trace_setevent @TraceID, 106, 11, @on
281 exec sp_trace_setevent @TraceID, 106, 12, @on
282 exec sp_trace_setevent @TraceID, 106, 14, @on
283 exec sp_trace_setevent @TraceID, 106, 8, @on
284 exec sp_trace_setevent @TraceID, 107, 26, @on
285 exec sp_trace_setevent @TraceID, 20, 1, @on
286 exec sp_trace_setevent @TraceID, 20, 9, @on
287 exec sp_trace_setevent @TraceID, 20, 11, @on
288 exec sp_trace_setevent @TraceID, 20, 6, @on
289 exec sp_trace_setevent @TraceID, 20, 10, @on
290 exec sp_trace_setevent @TraceID, 20, 12, @on
291 exec sp_trace_setevent @TraceID, 20, 14, @on
292 exec sp_trace_setevent @TraceID, 20, 8, @on
293 exec sp_trace_setevent @TraceID, 20, 26, @on
294 exec sp_trace_setevent @TraceID, 105, 11, @on
295 exec sp_trace_setevent @TraceID, 105, 12, @on
296 exec sp_trace_setevent @TraceID, 105, 6, @on
297 exec sp_trace_setevent @TraceID, 105, 9, @on
298 exec sp_trace_setevent @TraceID, 105, 10, @on
299 exec sp_trace_setevent @TraceID, 105, 14, @on
300 exec sp_trace_setevent @TraceID, 105, 8, @on
301 exec sp_trace_setevent @TraceID, 105, 26, @on
302 exec sp_trace_setevent @TraceID, 15, 11, @on
303 exec sp_trace_setevent @TraceID, 15, 6, @on
304 exec sp_trace_setevent @TraceID, 15, 9, @on
305 exec sp_trace_setevent @TraceID, 15, 10, @on
306 exec sp_trace_setevent @TraceID, 15, 12, @on
307 exec sp_trace_setevent @TraceID, 15, 14, @on
308 exec sp_trace_setevent @TraceID, 15, 15, @on
309 exec sp_trace_setevent @TraceID, 15, 8, @on
310 exec sp_trace_setevent @TraceID, 15, 26, @on
311 exec sp_trace_setevent @TraceID, 103, 1, @on
312 exec sp_trace_setevent @TraceID, 103, 9, @on
313 exec sp_trace_setevent @TraceID, 103, 2, @on
314 exec sp_trace_setevent @TraceID, 103, 6, @on
315 exec sp_trace_setevent @TraceID, 103, 10, @on
316 exec sp_trace_setevent @TraceID, 103, 11, @on
317 exec sp_trace_setevent @TraceID, 103, 12, @on
318 exec sp_trace_setevent @TraceID, 103, 14, @on
319 exec sp_trace_setevent @TraceID, 103, 8, @on
320 exec sp_trace_setevent @TraceID, 109, 26, @on
321 exec sp_trace_setevent @TraceID, 131, 1, @on
322 exec sp_trace_setevent @TraceID, 131, 11, @on
323 exec sp_trace_setevent @TraceID, 131, 12, @on
324 exec sp_trace_setevent @TraceID, 131, 6, @on
325 exec sp_trace_setevent @TraceID, 131, 10, @on
326 exec sp_trace_setevent @TraceID, 131, 14, @on
327 exec sp_trace_setevent @TraceID, 131, 8, @on
328 exec sp_trace_setevent @TraceID, 131, 26, @on
329 exec sp_trace_setevent @TraceID, 153, 1, @on
330 exec sp_trace_setevent @TraceID, 153, 11, @on
331 exec sp_trace_setevent @TraceID, 153, 12, @on
332 exec sp_trace_setevent @TraceID, 153, 6, @on
333 exec sp_trace_setevent @TraceID, 153, 10, @on
334 exec sp_trace_setevent @TraceID, 153, 14, @on
335 exec sp_trace_setevent @TraceID, 153, 8, @on
336 exec sp_trace_setevent @TraceID, 153, 26, @on
337 exec sp_trace_setevent @TraceID, 175, 1, @on
338 exec sp_trace_setevent @TraceID, 175, 11, @on
339 exec sp_trace_setevent @TraceID, 175, 12, @on
340 exec sp_trace_setevent @TraceID, 175, 6, @on
341 exec sp_trace_setevent @TraceID, 175, 10, @on
342 exec sp_trace_setevent @TraceID, 175, 14, @on
343 exec sp_trace_setevent @TraceID, 175, 8, @on
344 exec sp_trace_setevent @TraceID, 175, 26, @on
345 exec sp_trace_setevent @TraceID, 171, 1, @on
346 exec sp_trace_setevent @TraceID, 171, 9, @on
347 exec sp_trace_setevent @TraceID, 171, 2, @on
348 exec sp_trace_setevent @TraceID, 171, 10, @on
349 exec sp_trace_setevent @TraceID, 171, 6, @on
350 exec sp_trace_setevent @TraceID, 171, 11, @on
351 exec sp_trace_setevent @TraceID, 171, 12, @on
352 exec sp_trace_setevent @TraceID, 171, 14, @on
353 exec sp_trace_setevent @TraceID, 171, 8, @on
354 exec sp_trace_setevent @TraceID, 171, 26, @on
355 exec sp_trace_setevent @TraceID, 176, 1, @on
356 exec sp_trace_setevent @TraceID, 176, 9, @on
357 exec sp_trace_setevent @TraceID, 176, 11, @on
358 exec sp_trace_setevent @TraceID, 176, 6, @on
359 exec sp_trace_setevent @TraceID, 176, 10, @on
360 exec sp_trace_setevent @TraceID, 176, 12, @on
361 exec sp_trace_setevent @TraceID, 176, 14, @on
362 exec sp_trace_setevent @TraceID, 176, 8, @on
363 exec sp_trace_setevent @TraceID, 176, 26, @on
364 exec sp_trace_setevent @TraceID, 134, 1, @on
365 exec sp_trace_setevent @TraceID, 134, 11, @on
366 exec sp_trace_setevent @TraceID, 134, 12, @on
367 exec sp_trace_setevent @TraceID, 134, 6, @on
368 exec sp_trace_setevent @TraceID, 134, 10, @on
369 exec sp_trace_setevent @TraceID, 134, 14, @on
370 exec sp_trace_setevent @TraceID, 134, 8, @on
371 exec sp_trace_setevent @TraceID, 134, 26, @on
372 exec sp_trace_setevent @TraceID, 132, 1, @on
373 exec sp_trace_setevent @TraceID, 132, 2, @on
374 exec sp_trace_setevent @TraceID, 132, 10, @on
375 exec sp_trace_setevent @TraceID, 132, 11, @on
376 exec sp_trace_setevent @TraceID, 132, 6, @on
377 exec sp_trace_setevent @TraceID, 132, 12, @on
378 exec sp_trace_setevent @TraceID, 132, 14, @on
379 exec sp_trace_setevent @TraceID, 132, 8, @on
380 exec sp_trace_setevent @TraceID, 132, 26, @on
381 exec sp_trace_setevent @TraceID, 177, 1, @on
382 exec sp_trace_setevent @TraceID, 177, 9, @on
383 exec sp_trace_setevent @TraceID, 177, 6, @on
384 exec sp_trace_setevent @TraceID, 177, 10, @on
385 exec sp_trace_setevent @TraceID, 177, 11, @on
386 exec sp_trace_setevent @TraceID, 177, 12, @on
387 exec sp_trace_setevent @TraceID, 177, 14, @on
388 exec sp_trace_setevent @TraceID, 177, 8, @on
389 exec sp_trace_setevent @TraceID, 177, 26, @on
390 exec sp_trace_setevent @TraceID, 170, 1, @on
391 exec sp_trace_setevent @TraceID, 170, 9, @on
392 exec sp_trace_setevent @TraceID, 170, 2, @on
393 exec sp_trace_setevent @TraceID, 170, 10, @on
394 exec sp_trace_setevent @TraceID, 170, 6, @on
395 exec sp_trace_setevent @TraceID, 170, 11, @on
396 exec sp_trace_setevent @TraceID, 170, 12, @on
397 exec sp_trace_setevent @TraceID, 170, 14, @on
398 exec sp_trace_setevent @TraceID, 170, 8, @on
399 exec sp_trace_setevent @TraceID, 177, 26, @on
400 exec sp_trace_setevent @TraceID, 18, 6, @on
401 exec sp_trace_setevent @TraceID, 18, 14, @on
402 exec sp_trace_setevent @TraceID, 18, 9, @on
403 exec sp_trace_setevent @TraceID, 18, 10, @on
404 exec sp_trace_setevent @TraceID, 18, 11, @on
405 exec sp_trace_setevent @TraceID, 18, 12, @on
406 exec sp_trace_setevent @TraceID, 18, 8, @on
407 exec sp_trace_setevent @TraceID, 18, 26, @on
408 exec sp_trace_setevent @TraceID, 113, 1, @on
409 exec sp_trace_setevent @TraceID, 113, 9, @on
410 exec sp_trace_setevent @TraceID, 113, 2, @on
411 exec sp_trace_setevent @TraceID, 113, 6, @on
412 exec sp_trace_setevent @TraceID, 113, 10, @on
413 exec sp_trace_setevent @TraceID, 113, 11, @on
414 exec sp_trace_setevent @TraceID, 113, 12, @on
415 exec sp_trace_setevent @TraceID, 113, 14, @on
416 exec sp_trace_setevent @TraceID, 113, 8, @on
417 exec sp_trace_setevent @TraceID, 113, 26, @on
418
419 -- Set the Filters
420 declare @intfilter int
421 declare @bigintfilter bigint
422
423 exec sp_trace_setfilter @TraceID, 10, 0, 7, N''NBC_Trace''
424 -- Set the trace status to start
425 exec sp_trace_setstatus @TraceID, 1
426
427 -- display trace id for future references
428 --select TraceID=@TraceID
429 end
430 else
431 begin
432 insert into prism.dbo.systemlog (apptext,moduleid) values(''SST: No previous backups have been ran. Please run at least one backup before running this script.'',775)
433 return 0
434 end
435
436 end')
437
438 exec sp_procoption 'NBC_Trace', 'startup', 'true'
439
440 exec master.dbo.NBC_Trace
441end
442else
443begin
444 insert into prism.dbo.systemlog (apptext,moduleid) values('SST: SQL Server is Express edition. Server Side Trace cannot be setup.',775)
445end
446
447GO