September 16, 2011 at 12:34 pm
So I find myself needing to monitor how much a database user role is getting used and more specifically, if an application is switching the contents of the role and how.
Anyone got any thoughts on how to do this?
Thanks,
Fraggle
September 19, 2011 at 1:55 pm
Hmm, not sure of your terminology. What exactly do you mean by "contents of a role"?
If you are looking to track changes to the permissions assigned to a particular role, the good news is that you are probably already tracking this information via the default trace- most likely all you need to do is filter the information captured by the default trace for the events you are interested in.
You can do something like the following... I used this in a case where the requirement was to send emails daily listing any permissions changes in the past 48 hours (excluding some cases)... this is not perfect - I'm just providing it as a starting point that you can build from. also I should probably credit someone, because this is frankensteined from code all over sql server central, so I have no idea who to credit... use at your own risk and all that jazz.
first, create a database called db_permissions_audit_test then:
USE [db_permissions_audit_test]
GO
/****** Object: Table [dbo].[DefaultTraceData] Script Date: 09/19/2011 15:47:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DefaultTraceData](
[EventClassName] [nvarchar](128) NULL,
[EventSubClassName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[BinaryData] [image] NULL,
[DatabaseID] [int] NULL,
[TransactionID] [bigint] NULL,
[LineNumber] [int] NULL,
[NTUserName] [nvarchar](256) NULL,
[NTDomainName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
[ClientProcessID] [int] NULL,
[ApplicationName] [nvarchar](256) NULL,
[LoginName] [nvarchar](256) NULL,
[SPID] [int] NULL,
[Duration] [bigint] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[CPU] [int] NULL,
[Permissions] [bigint] NULL,
[Severity] [int] NULL,
[EventSubClass] [int] NULL,
[ObjectID] [int] NULL,
[Success] [int] NULL,
[IndexID] [int] NULL,
[IntegerData] [int] NULL,
[ServerName] [nvarchar](256) NULL,
[EventClass] [int] NULL,
[ObjectType] [int] NULL,
[NestLevel] [int] NULL,
[State] [int] NULL,
[Error] [int] NULL,
[Mode] [int] NULL,
[Handle] [int] NULL,
[ObjectName] [nvarchar](256) NULL,
[DatabaseName] [nvarchar](256) NULL,
[FileName] [nvarchar](256) NULL,
[OwnerName] [nvarchar](256) NULL,
[RoleName] [nvarchar](256) NULL,
[TargetUserName] [nvarchar](256) NULL,
[DBUserName] [nvarchar](256) NULL,
[LoginSid] [image] NULL,
[TargetLoginName] [nvarchar](256) NULL,
[TargetLoginSid] [image] NULL,
[ColumnPermissions] [int] NULL,
[LinkedServerName] [nvarchar](256) NULL,
[ProviderName] [nvarchar](256) NULL,
[MethodName] [nvarchar](256) NULL,
[RowCounts] [bigint] NULL,
[RequestID] [int] NULL,
[XactSequence] [bigint] NULL,
[EventSequence] [bigint] NULL,
[BigintData1] [bigint] NULL,
[BigintData2] [bigint] NULL,
[GUID] [uniqueidentifier] NULL,
[IntegerData2] [int] NULL,
[ObjectID2] [bigint] NULL,
[Type] [int] NULL,
[OwnerID] [int] NULL,
[ParentName] [nvarchar](256) NULL,
[IsSystem] [int] NULL,
[Offset] [int] NULL,
[SourceDatabaseID] [int] NULL,
[SqlHandle] [image] NULL,
[SessionLoginName] [nvarchar](256) NULL,
[PlanHandle] [image] NULL,
[GroupID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [db_permissions_audit_test]
GO
/****** Object: Table [dbo].[tracedataarchive] Script Date: 09/19/2011 15:51:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tracedataarchive](
[EventClassName] [nvarchar](128) NULL,
[EventSubClassName] [nvarchar](128) NULL,
[TextData] [ntext] NULL,
[BinaryData] [image] NULL,
[DatabaseID] [int] NULL,
[TransactionID] [bigint] NULL,
[LineNumber] [int] NULL,
[NTUserName] [nvarchar](256) NULL,
[NTDomainName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
[ClientProcessID] [int] NULL,
[ApplicationName] [nvarchar](256) NULL,
[LoginName] [nvarchar](256) NULL,
[SPID] [int] NULL,
[Duration] [bigint] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[CPU] [int] NULL,
[Permissions] [bigint] NULL,
[Severity] [int] NULL,
[EventSubClass] [int] NULL,
[ObjectID] [int] NULL,
[Success] [int] NULL,
[IndexID] [int] NULL,
[IntegerData] [int] NULL,
[ServerName] [nvarchar](256) NULL,
[EventClass] [int] NULL,
[ObjectType] [int] NULL,
[NestLevel] [int] NULL,
[State] [int] NULL,
[Error] [int] NULL,
[Mode] [int] NULL,
[Handle] [int] NULL,
[ObjectName] [nvarchar](256) NULL,
[DatabaseName] [nvarchar](256) NULL,
[FileName] [nvarchar](256) NULL,
[OwnerName] [nvarchar](256) NULL,
[RoleName] [nvarchar](256) NULL,
[TargetUserName] [nvarchar](256) NULL,
[DBUserName] [nvarchar](256) NULL,
[LoginSid] [image] NULL,
[TargetLoginName] [nvarchar](256) NULL,
[TargetLoginSid] [image] NULL,
[ColumnPermissions] [int] NULL,
[LinkedServerName] [nvarchar](256) NULL,
[ProviderName] [nvarchar](256) NULL,
[MethodName] [nvarchar](256) NULL,
[RowCounts] [bigint] NULL,
[RequestID] [int] NULL,
[XactSequence] [bigint] NULL,
[EventSequence] [bigint] NULL,
[BigintData1] [bigint] NULL,
[BigintData2] [bigint] NULL,
[GUID] [uniqueidentifier] NULL,
[IntegerData2] [int] NULL,
[ObjectID2] [bigint] NULL,
[Type] [int] NULL,
[OwnerID] [int] NULL,
[ParentName] [nvarchar](256) NULL,
[IsSystem] [int] NULL,
[Offset] [int] NULL,
[SourceDatabaseID] [int] NULL,
[SqlHandle] [image] NULL,
[SessionLoginName] [nvarchar](256) NULL,
[PlanHandle] [image] NULL,
[GroupID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [db_permissions_audit_test]
GO
/****** Object: StoredProcedure [dbo].[ChangedPermissions48hours] Script Date: 09/19/2011 15:45:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[ChangedPermissions48hours]
as
begin
-- this proc imports data from the default trace. Then it searches for any permissoins changes and emails a report.
-- default trace writes to E:\MSSQLServer2008\MSSQL10.MSSQLSERVER\MSSQL\Log\log###.trc
--this imports all trace data, so takes 1-2 minutes for the import to complete
/**archive current records**/
INSERT INTO [db_permissions_audit_test].[dbo].[tracedataarchive]
([EventClassName]
,[EventSubClassName]
,[TextData]
,[BinaryData]
,[DatabaseID]
,[TransactionID]
,[LineNumber]
,[NTUserName]
,[NTDomainName]
,[HostName]
,[ClientProcessID]
,[ApplicationName]
,[LoginName]
,[SPID]
,[Duration]
,[StartTime]
,[EndTime]
,[Reads]
,[Writes]
,[CPU]
,[Permissions]
,[Severity]
,[EventSubClass]
,[ObjectID]
,[Success]
,[IndexID]
,[IntegerData]
,[ServerName]
,[EventClass]
,[ObjectType]
,[NestLevel]
,[State]
,[Error]
,[Mode]
,[Handle]
,[ObjectName]
,[DatabaseName]
,[FileName]
,[OwnerName]
,[RoleName]
,[TargetUserName]
,[DBUserName]
,[LoginSid]
,[TargetLoginName]
,[TargetLoginSid]
,[ColumnPermissions]
,[LinkedServerName]
,[ProviderName]
,[MethodName]
,[RowCounts]
,[RequestID]
,[XactSequence]
,[EventSequence]
,[BigintData1]
,[BigintData2]
,[GUID]
,[IntegerData2]
,[ObjectID2]
,[Type]
,[OwnerID]
,[ParentName]
,[IsSystem]
,[Offset]
,[SourceDatabaseID]
,[SqlHandle]
,[SessionLoginName]
,[PlanHandle]
,[GroupID])
SELECT [EventClassName]
,[EventSubClassName]
,[TextData]
,[BinaryData]
,[DatabaseID]
,[TransactionID]
,[LineNumber]
,[NTUserName]
,[NTDomainName]
,[HostName]
,[ClientProcessID]
,[ApplicationName]
,[LoginName]
,[SPID]
,[Duration]
,[StartTime]
,[EndTime]
,[Reads]
,[Writes]
,[CPU]
,[Permissions]
,[Severity]
,[EventSubClass]
,[ObjectID]
,[Success]
,[IndexID]
,[IntegerData]
,[ServerName]
,[EventClass]
,[ObjectType]
,[NestLevel]
,[State]
,[Error]
,[Mode]
,[Handle]
,[ObjectName]
,[DatabaseName]
,[FileName]
,[OwnerName]
,[RoleName]
,[TargetUserName]
,[DBUserName]
,[LoginSid]
,[TargetLoginName]
,[TargetLoginSid]
,[ColumnPermissions]
,[LinkedServerName]
,[ProviderName]
,[MethodName]
,[RowCounts]
,[RequestID]
,[XactSequence]
,[EventSequence]
,[BigintData1]
,[BigintData2]
,[GUID]
,[IntegerData2]
,[ObjectID2]
,[Type]
,[OwnerID]
,[ParentName]
,[IsSystem]
,[Offset]
,[SourceDatabaseID]
,[SqlHandle]
,[SessionLoginName]
,[PlanHandle]
,[GroupID]
FROM [db_permissions_audit_test].[dbo].[DefaultTraceData]
where [StartTime] >(select MAX([StartTime]) from [db_permissions_audit_test].[dbo].[tracedataarchive])
/** clear existing data **/
drop table DefaultTraceData
/**find name of oldest .trc file**/
/**bring in data from all .trc files in directory (single file name brings in all log###.trc files)**/
SELECT te.name AS EventClassName
,tsv.subclass_name AS EventSubClassName
,t.*
INTO DefaultTraceData
FROM fn_trace_gettable('E:\MSSQLServer2008\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc',
DEFAULT) AS t
JOIN sys.trace_events AS te ON te.trace_event_id = t.EventClass
LEFT JOIN sys.trace_subclass_values AS tsv
ON tsv.trace_event_id = t.EventClass
AND tsv.subclass_value = t.EventSubClass ;
/*table looks wrong if there are null values; run update to replace nulls with "Not Applicable"**/
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET eventclassname = 'NOT APPLICABLE'
Where eventclassname is null
--and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET eventsubclassname = 'NOT APPLICABLE'
Where eventsubclassname is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET textdata = 'NOT APPLICABLE'
Where textdata is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET rolename = 'NOT APPLICABLE'
Where rolename is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET ntusername = 'NOT APPLICABLE'
Where ntusername is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET hostname = 'NOT APPLICABLE'
Where hostname is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET loginname = 'NOT APPLICABLE'
Where loginname is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET starttime = 'NOT APPLICABLE'
Where starttime is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET databasename = 'NOT APPLICABLE'
Where databasename is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET targetusername = 'NOT APPLICABLE'
Where targetusername is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
UPDATE [db_permissions_audit_test].[dbo].[DefaultTraceData]
SET targetloginname = 'NOT APPLICABLE'
Where targetloginname is null
and starttime > DATEADD(hh, - 48, GETDATE())
AND
eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
/******Genereate Report*************************/
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Changed Permissions Last 48 Hours</H1>' +
N'<table border="1">' +
N'<tr><th>EventType</th><th>EventSubtype</th>' +
N'<th>Details</th><th>Rolename</th><th>NTusername</th>' +
N'<th>hostname</th><th>loginname</th><th>starttime</th>' +
N'<th>databasename</th><th>targetusername</th><th>targetloginame</th></tr>' +
CAST ( (
select
td= eventclassname, '',
td= eventsubclassname, '',
td= textdata, '',
td= rolename, '',
td= ntusername, '',
td= hostname, '',
td= loginname, '',
td= starttime, '',
td= databasename, '',
td= targetusername, '',
td= targetloginname
from DefaultTraceData where eventclass in (
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
--restrict to last 48 hours and sort
and starttime > DATEADD(hh, - 48, GETDATE())
and textdata not like '%diagram%'
and textdata not like '%grant execute on dbo.dt_%'
order by starttime desc
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
if(
select
COUNT(*)
from DefaultTraceData where eventclass in
(
'170',
'171',
'172',
'173',
'174',
--'175',
'176',
'177',
'178',
'134',
'129',
'130',
'131',
'102',
'103',
'104',
'105',
'106',
'108',
'109',
'110',
'111',
'113',
'135')
--restrict to last 48 hours and sort
and starttime > DATEADD(hh, - 48, GETDATE())
and textdata not like '%diagram%'
and textdata not like '%grant execute on dbo.dt_%'
)!=0
begin
declare @subj1 varchar (1000);
select @subj1= 'changed Permissions Last 48 Hours on-diagrams excluded- '+@@servername
EXEC msdb.dbo.sp_send_dbmail @recipients='EMAILADDRESSYOUWANTOTSENDTO@SOMEWHERE.COM',
@subject = @subj1,
@PROFILE_NAME ='DBMAILPROFILENAME',
@body = @tableHTML,
@body_format = 'HTML'
;
end
else
begin
declare @subj3 varchar (1000);
select @subj3= 'NO CHANGED Permissions in Last 48 Hours on '+@@servername
EXEC msdb.dbo.sp_send_dbmail @recipients='EMAILADDRESSYOUWANTOTSENDTO@SOMEWHERE.COM',
@subject = @subj3,
@PROFILE_NAME ='DBMAILPROFILENAME2',
@body = 'NO CHANGED Permissions in Last 48 Hours other than grants or denys on objects like %diagram%'
end
--select * from msdb.dbo.sysmail_allitems
/***
Following is complete event class listing for reference purposes only... in case we want to change what we are reporting on... this code is not used at all.
found it at http://www.sqlservercentral.com/Forums/Topic429905-146-1.aspx
INSERT INTO #EventClass (EventClass, EventName) VALUES (10, 'RPC:Completed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (11, 'RPC:Starting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (12, 'SQL:BatchCompleted')
INSERT INTO #EventClass (EventClass, EventName) VALUES (13, 'SQL:BatchStarting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (14, 'Audit Login')
INSERT INTO #EventClass (EventClass, EventName) VALUES (15, 'Audit Logout')
INSERT INTO #EventClass (EventClass, EventName) VALUES (16, 'Attention')
INSERT INTO #EventClass (EventClass, EventName) VALUES (17, 'ExistingConnection')
INSERT INTO #EventClass (EventClass, EventName) VALUES (18, 'Audit Server Starts And Stops')
INSERT INTO #EventClass (EventClass, EventName) VALUES (19, 'DTCTransaction')
INSERT INTO #EventClass (EventClass, EventName) VALUES (20, 'Audit Login Failed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (21, 'EventLog')
INSERT INTO #EventClass (EventClass, EventName) VALUES (22, 'ErrorLog')
INSERT INTO #EventClass (EventClass, EventName) VALUES (23, 'Lock:Released')
INSERT INTO #EventClass (EventClass, EventName) VALUES (24, 'Lock:Acquired')
INSERT INTO #EventClass (EventClass, EventName) VALUES (25, 'Lock:Deadlock')
INSERT INTO #EventClass (EventClass, EventName) VALUES (26, 'Lock:Cancel')
INSERT INTO #EventClass (EventClass, EventName) VALUES (27, 'Lock:Timeout')
INSERT INTO #EventClass (EventClass, EventName) VALUES (28, 'Degree of Parallelism (7.0 Insert)')
INSERT INTO #EventClass (EventClass, EventName) VALUES (33, 'Exception')
INSERT INTO #EventClass (EventClass, EventName) VALUES (34, 'SP:CacheMiss')
INSERT INTO #EventClass (EventClass, EventName) VALUES (35, 'SP:CacheInsert')
INSERT INTO #EventClass (EventClass, EventName) VALUES (36, 'SP:CacheRemove')
INSERT INTO #EventClass (EventClass, EventName) VALUES (37, 'SP:Recompile')
INSERT INTO #EventClass (EventClass, EventName) VALUES (38, 'SP:CacheHit')
INSERT INTO #EventClass (EventClass, EventName) VALUES (39, 'Deprecated')
INSERT INTO #EventClass (EventClass, EventName) VALUES (40, 'SQL:StmtStarting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (41, 'SQL:StmtCompleted')
INSERT INTO #EventClass (EventClass, EventName) VALUES (42, 'SP:Starting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (43, 'SP:Completed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (44, 'SP:StmtStarting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (45, 'SP:StmtCompleted')
INSERT INTO #EventClass (EventClass, EventName) VALUES (46, 'Object:Created')
INSERT INTO #EventClass (EventClass, EventName) VALUES (47, 'Object:Deleted')
INSERT INTO #EventClass (EventClass, EventName) VALUES (50, 'SQLTransaction')
INSERT INTO #EventClass (EventClass, EventName) VALUES (51, 'Scan:Started')
INSERT INTO #EventClass (EventClass, EventName) VALUES (52, 'Scan:Stopped')
INSERT INTO #EventClass (EventClass, EventName) VALUES (53, 'CursorOpen')
INSERT INTO #EventClass (EventClass, EventName) VALUES (54, 'TransactionLog')
INSERT INTO #EventClass (EventClass, EventName) VALUES (55, 'Hash Warning')
INSERT INTO #EventClass (EventClass, EventName) VALUES (58, 'Auto Stats')
INSERT INTO #EventClass (EventClass, EventName) VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO #EventClass (EventClass, EventName) VALUES (60, 'Lock:Escalation')
INSERT INTO #EventClass (EventClass, EventName) VALUES (61, 'OLEDB Errors')
INSERT INTO #EventClass (EventClass, EventName) VALUES (67, 'Execution Warnings')
INSERT INTO #EventClass (EventClass, EventName) VALUES (68, 'Showplan Text (Unencoded)')
INSERT INTO #EventClass (EventClass, EventName) VALUES (69, 'Sort Warnings')
INSERT INTO #EventClass (EventClass, EventName) VALUES (70, 'CursorPrepare')
INSERT INTO #EventClass (EventClass, EventName) VALUES (71, 'Prepare SQL')
INSERT INTO #EventClass (EventClass, EventName) VALUES (72, 'Exec Prepared SQL')
INSERT INTO #EventClass (EventClass, EventName) VALUES (73, 'Unprepare SQL')
INSERT INTO #EventClass (EventClass, EventName) VALUES (74, 'CursorExecute')
INSERT INTO #EventClass (EventClass, EventName) VALUES (75, 'CursorRecompile')
INSERT INTO #EventClass (EventClass, EventName) VALUES (76, 'CursorImplicitConversion')
INSERT INTO #EventClass (EventClass, EventName) VALUES (77, 'CursorUnprepare')
INSERT INTO #EventClass (EventClass, EventName) VALUES (78, 'CursorClose')
INSERT INTO #EventClass (EventClass, EventName) VALUES (79, 'Missing Column Statistics')
INSERT INTO #EventClass (EventClass, EventName) VALUES (80, 'Missing Join Predicate')
INSERT INTO #EventClass (EventClass, EventName) VALUES (81, 'Server Memory Change')
INSERT INTO #EventClass (EventClass, EventName) VALUES (82, 'UserConfigurable:0')
INSERT INTO #EventClass (EventClass, EventName) VALUES (83, 'UserConfigurable:1')
INSERT INTO #EventClass (EventClass, EventName) VALUES (84, 'UserConfigurable:2')
INSERT INTO #EventClass (EventClass, EventName) VALUES (85, 'UserConfigurable:3')
INSERT INTO #EventClass (EventClass, EventName) VALUES (86, 'UserConfigurable:4')
INSERT INTO #EventClass (EventClass, EventName) VALUES (87, 'UserConfigurable:5')
INSERT INTO #EventClass (EventClass, EventName) VALUES (88, 'UserConfigurable:6')
INSERT INTO #EventClass (EventClass, EventName) VALUES (89, 'UserConfigurable:7')
INSERT INTO #EventClass (EventClass, EventName) VALUES (90, 'UserConfigurable:8')
INSERT INTO #EventClass (EventClass, EventName) VALUES (91, 'UserConfigurable:9')
INSERT INTO #EventClass (EventClass, EventName) VALUES (92, 'Data File Auto Grow')
INSERT INTO #EventClass (EventClass, EventName) VALUES (93, 'Log File Auto Grow')
INSERT INTO #EventClass (EventClass, EventName) VALUES (94, 'Data File Auto Shrink')
INSERT INTO #EventClass (EventClass, EventName) VALUES (95, 'Log File Auto Shrink')
INSERT INTO #EventClass (EventClass, EventName) VALUES (96, 'Showplan Text')
INSERT INTO #EventClass (EventClass, EventName) VALUES (97, 'Showplan All')
INSERT INTO #EventClass (EventClass, EventName) VALUES (98, 'Showplan Statistics Profile')
INSERT INTO #EventClass (EventClass, EventName) VALUES (100, 'RPC Output Parameter')
INSERT INTO #EventClass (EventClass, EventName) VALUES (102, 'Audit Database Scope GDR Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (103, 'Audit Schema Object GDR Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (104, 'Audit Addlogin Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (105, 'Audit Login GDR Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (106, 'Audit Login Change Property Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (107, 'Audit Login Change Password Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (108, 'Audit Add Login to Server Role Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (109, 'Audit Add DB User Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (110, 'Audit Add Member to DB Role Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (111, 'Audit Add Role Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (112, 'Audit App Role Change Password Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (113, 'Audit Statement Permission Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (114, 'Audit Schema Object Access Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (115, 'Audit Backup/Restore Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (116, 'Audit DBCC Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (117, 'Audit Change Audit Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (118, 'Audit Object Derived Permission Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (119, 'OLEDB Call Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (120, 'OLEDB QueryInterface Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (121, 'OLEDB DataRead Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (122, 'Showplan XML')
INSERT INTO #EventClass (EventClass, EventName) VALUES (123, 'SQL:FullTextQuery')
INSERT INTO #EventClass (EventClass, EventName) VALUES (124, 'Broker:Conversation')
INSERT INTO #EventClass (EventClass, EventName) VALUES (125, 'Deprecation Announcement')
INSERT INTO #EventClass (EventClass, EventName) VALUES (126, 'Deprecation Final Support')
INSERT INTO #EventClass (EventClass, EventName) VALUES (127, 'Exchange Spill Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (128, 'Audit Database Management Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (129, 'Audit Database Object Management Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (130, 'Audit Database Principal Management Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (131, 'Audit Schema Object Management Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (132, 'Audit Server Principal Impersonation Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (133, 'Audit Database Principal Impersonation Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (134, 'Audit Server Object Take Ownership Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (135, 'Audit Database Object Take Ownership Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (136, 'Broker:Conversation Group')
INSERT INTO #EventClass (EventClass, EventName) VALUES (137, 'Blocked process report')
INSERT INTO #EventClass (EventClass, EventName) VALUES (138, 'Broker:Connection')
INSERT INTO #EventClass (EventClass, EventName) VALUES (139, 'Broker:Forwarded Message Sent')
INSERT INTO #EventClass (EventClass, EventName) VALUES (140, 'Broker:Forwarded Message Dropped')
INSERT INTO #EventClass (EventClass, EventName) VALUES (141, 'Broker:Message Classify')
INSERT INTO #EventClass (EventClass, EventName) VALUES (142, 'Broker:Transmission')
INSERT INTO #EventClass (EventClass, EventName) VALUES (143, 'Broker:Queue Disabled')
INSERT INTO #EventClass (EventClass, EventName) VALUES (144, 'Broker:Mirrored Route State Changed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (146, 'Showplan XML Statistics Profile')
INSERT INTO #EventClass (EventClass, EventName) VALUES (148, 'Deadlock graph')
INSERT INTO #EventClass (EventClass, EventName) VALUES (149, 'Broker:Remote Message Acknowledgement')
INSERT INTO #EventClass (EventClass, EventName) VALUES (150, 'Trace File Close')
INSERT INTO #EventClass (EventClass, EventName) VALUES (152, 'Audit Change Database Owner')
INSERT INTO #EventClass (EventClass, EventName) VALUES (153, 'Audit Schema Object Take Ownership Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (155, 'FT:Crawl Started')
INSERT INTO #EventClass (EventClass, EventName) VALUES (156, 'FT:Crawl Stopped')
INSERT INTO #EventClass (EventClass, EventName) VALUES (157, 'FT:Crawl Aborted')
INSERT INTO #EventClass (EventClass, EventName) VALUES (158, 'Audit Broker Conversation')
INSERT INTO #EventClass (EventClass, EventName) VALUES (159, 'Audit Broker Login')
INSERT INTO #EventClass (EventClass, EventName) VALUES (160, 'Broker:Message Undeliverable')
INSERT INTO #EventClass (EventClass, EventName) VALUES (161, 'Broker:Corrupted Message')
INSERT INTO #EventClass (EventClass, EventName) VALUES (162, 'User Error Message')
INSERT INTO #EventClass (EventClass, EventName) VALUES (163, 'Broker:Activation')
INSERT INTO #EventClass (EventClass, EventName) VALUES (164, 'Object:Altered')
INSERT INTO #EventClass (EventClass, EventName) VALUES (165, 'Performance statistics')
INSERT INTO #EventClass (EventClass, EventName) VALUES (166, 'SQL:StmtRecompile')
INSERT INTO #EventClass (EventClass, EventName) VALUES (167, 'Database Mirroring State Change')
INSERT INTO #EventClass (EventClass, EventName) VALUES (168, 'Showplan XML For Query Compile')
INSERT INTO #EventClass (EventClass, EventName) VALUES (169, 'Showplan All For Query Compile')
INSERT INTO #EventClass (EventClass, EventName) VALUES (170, 'Audit Server Scope GDR Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (171, 'Audit Server Object GDR Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (172, 'Audit Database Object GDR Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (173, 'Audit Server Operation Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (175, 'Audit Server Alter Trace Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (176, 'Audit Server Object Management Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (177, 'Audit Server Principal Management Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (178, 'Audit Database Operation Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (180, 'Audit Database Object Access Event')
INSERT INTO #EventClass (EventClass, EventName) VALUES (181, 'TM: Begin Tran starting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (182, 'TM: Begin Tran completed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (183, 'TM: Promote Tran starting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (184, 'TM: Promote Tran completed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (185, 'TM: Commit Tran starting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (186, 'TM: Commit Tran completed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (187, 'TM: Rollback Tran starting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (188, 'TM: Rollback Tran completed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (189, 'Lock:Timeout (timeout > 0)')
INSERT INTO #EventClass (EventClass, EventName) VALUES (190, 'Progress Report: Online Index Operation')
INSERT INTO #EventClass (EventClass, EventName) VALUES (191, 'TM: Save Tran starting')
INSERT INTO #EventClass (EventClass, EventName) VALUES (192, 'TM: Save Tran completed')
INSERT INTO #EventClass (EventClass, EventName) VALUES (193, 'Background Job Error')
INSERT INTO #EventClass (EventClass, EventName) VALUES (194, 'OLEDB Provider Information')
INSERT INTO #EventClass (EventClass, EventName) VALUES (195, 'Mount Tape')
INSERT INTO #EventClass (EventClass, EventName) VALUES (196, 'Assembly Load')
INSERT INTO #EventClass (EventClass, EventName) VALUES (198, 'XQuery Static Type')
INSERT INTO #EventClass (EventClass, EventName) VALUES (199, 'QN: Subscription')
INSERT INTO #EventClass (EventClass, EventName) VALUES (200, 'QN: Parameter table')
INSERT INTO #EventClass (EventClass, EventName) VALUES (201, 'QN: Template')
INSERT INTO #EventClass (EventClass, EventName) VALUES (202, 'QN: Dynamics')
***/
end
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply