CREATE PROCEDURE SegSOyApp_DataReduce @TracePath varchar(1024), @DatabaseName nvarchar(256) AS
BEGIN
	
	DECLARE @TraceName varchar(1100)
	DECLARE @TraceId int
	DECLARE @TS datetime
	DECLARE @NextId int
	DECLARE @TraceDeleteCmd varchar(1100)
	
	IF SUBSTRING(@TracePath,LEN(@TracePath),1) <> '\'
		SET @TracePath = @TracePath + '\'
		
	SET @TraceName = @TracePath + @DatabaseName + '_SegSO_EffPerm.trc'
	
	SET @TraceId = (select traceid from ::fn_trace_getinfo(0) where property = 2 and value = @TraceName)
	
	IF @TraceId IS NOT NULL BEGIN
	
		IF (select COUNT(*) from sys.objects where name = 'Perm' and type = 'U') > 0
			DROP TABLE Perm

		IF (select COUNT(*) from sys.objects where name = 'ObjType' and type = 'U') > 0
			DROP TABLE ObjType
		
		IF (select COUNT(*) from sys.objects where name = 'EvtSubClass' and type = 'U') > 0
			DROP TABLE EvtSubClass
		
		CREATE TABLE Perm (id int, description varchar(255))
		INSERT INTO Perm values (1,'SELECT')
		INSERT INTO Perm values (2,'UPDATE')
		INSERT INTO Perm values (4,'REFERENCES')
		INSERT INTO Perm values (8,'INSERT')
		INSERT INTO Perm values (16,'DELETE')
		INSERT INTO Perm values (32,'EXECUTE')

		CREATE TABLE ObjType (id int, description varchar(255))
		INSERT INTO ObjType values(8259, 'Check Constraint')
		INSERT INTO ObjType values(8260, 'Default (constraint or standalone)')
		INSERT INTO ObjType values(8262, 'Foreign-key Constraint')
		INSERT INTO ObjType values(8272, 'Stored Procedure')
		INSERT INTO ObjType values(8274, 'Rule')
		INSERT INTO ObjType values(8275, 'System Table')
		INSERT INTO ObjType values(8276, 'Trigger on Server')
		INSERT INTO ObjType values(8277, '(User-defined) Table')
		INSERT INTO ObjType values(8278, 'View')
		INSERT INTO ObjType values(8280, 'Extended Stored Procedure')
		INSERT INTO ObjType values(16724, 'CLR Trigger')
		INSERT INTO ObjType values(16964, 'Database')
		INSERT INTO ObjType values(16975, 'Object')
		INSERT INTO ObjType values(17222, 'FullText Catalog')
		INSERT INTO ObjType values(17232, 'CLR Stored Procedure')
		INSERT INTO ObjType values(17235, 'Schema')
		INSERT INTO ObjType values(17475, 'Credential')
		INSERT INTO ObjType values(17491, 'DDL Event')
		INSERT INTO ObjType values(17741, 'Management Event')
		INSERT INTO ObjType values(17747, 'Security Event')
		INSERT INTO ObjType values(17985, 'CLR Aggregate Function')
		INSERT INTO ObjType values(17993, 'Inline Table-valued SQL Function')
		INSERT INTO ObjType values(18000, 'Partition Function')
		INSERT INTO ObjType values(18002, 'Replication Filter Procedure')
		INSERT INTO ObjType values(18004, 'Table-valued SQL Function')
		INSERT INTO ObjType values(18259, 'Server Role')
		INSERT INTO ObjType values(18263, 'Microsoft Windows Group')
		INSERT INTO ObjType values(19265, 'Asymmetric Key')
		INSERT INTO ObjType values(19277, 'Master Key')
		INSERT INTO ObjType values(19280, 'Primary Key')
		INSERT INTO ObjType values(19283, 'ObfusKey')
		INSERT INTO ObjType values(19521, 'Asymmetric Key Login')
		INSERT INTO ObjType values(19523, 'Certificate Login')
		INSERT INTO ObjType values(19538, 'Role')
		INSERT INTO ObjType values(19539, 'SQL Login')
		INSERT INTO ObjType values(19543, 'Windows Login')
		INSERT INTO ObjType values(20034, 'Remote Service Binding')
		INSERT INTO ObjType values(20036, 'Event Notification on Database')
		INSERT INTO ObjType values(20037, 'Event Notification')
		INSERT INTO ObjType values(20038, 'Scalar SQL Function')
		INSERT INTO ObjType values(20047, 'Event Notification on Object')
		INSERT INTO ObjType values(20051, 'Synonym')
		INSERT INTO ObjType values(20549, 'End Point')
		INSERT INTO ObjType values(20801, 'Adhoc Queries which may be cached')
		INSERT INTO ObjType values(20816, 'Prepared Queries which may be cached')
		INSERT INTO ObjType values(20819, 'Service Broker Service Queue')
		INSERT INTO ObjType values(20821, 'Unique Constraint')
		INSERT INTO ObjType values(21057, 'Application Role')
		INSERT INTO ObjType values(21059, 'Certificate')
		INSERT INTO ObjType values(21075, 'Server')
		INSERT INTO ObjType values(21076, 'Transact-SQL Trigger')
		INSERT INTO ObjType values(21313, 'Assembly')
		INSERT INTO ObjType values(21318, 'CLR Scalar Function')
		INSERT INTO ObjType values(21321, 'Inline scalar SQL Function')
		INSERT INTO ObjType values(21328, 'Partition Scheme')
		INSERT INTO ObjType values(21333, 'User')
		INSERT INTO ObjType values(21571, 'Service Broker Service Contract')
		INSERT INTO ObjType values(21572, 'Trigger on Database')
		INSERT INTO ObjType values(21574, 'CLR Table-valued Function')
		INSERT INTO ObjType values(21577, 'Internal Table')
		INSERT INTO ObjType values(21581, 'Service Broker Message Type')
		INSERT INTO ObjType values(21586, 'Service Broker Route')
		INSERT INTO ObjType values(21587, 'Statistics')
		INSERT INTO ObjType values(22099, 'Service Broker Service')
		INSERT INTO ObjType values(22601, 'Index')
		INSERT INTO ObjType values(22604, 'Certificate Login')
		INSERT INTO ObjType values(22611, 'XMLSchema')
		INSERT INTO ObjType values(22868, 'Type')

		CREATE TABLE EvtSubClass (id int, description varchar(255))
		INSERT INTO EvtSubClass values(1,'CREATE')
		INSERT INTO EvtSubClass values(2,'ALTER')
		INSERT INTO EvtSubClass values(3,'DROP')
		INSERT INTO EvtSubClass values(4,'DUMP')
		INSERT INTO EvtSubClass values(11,'LOAD')
		
		IF (select COUNT(*) from sys.objects where name = 'SegSO_EffPerm' and type = 'U') = 0
			CREATE TABLE SegSO_EffPerm (
				ExcId int,
				DatabaseName varchar(1024),
				TimeStamp datetime,
				OperationClass varchar(1024),
				ApplicationName varchar(1024),
				LoginName varchar(1024),
				ObjectName varchar(1024),
				ObjectType varchar(1024),
				OperationType varchar(1024),
				Success int)
		

		exec master..sp_trace_setstatus @TraceId,0
		exec master..sp_trace_setstatus @TraceId,2

		SET @TS = GETDATE()

		SET @NextId = (select MAX(ExcId)+1 from SegSO_EffPerm where DatabaseName = @DatabaseName)

		IF @NextId IS NULL
			SET @NextId = 1

		INSERT INTO SegSO_EffPerm
			select @NextId, @DatabaseName, @TS, 'Data Manipulation Language',TRC.ApplicationName,TRC.LoginName,TRC.ObjectName,OT.Description,PM.description,TRC.Success
			from ::fn_trace_gettable(@TraceName,default) TRC, Perm PM, ObjType OT
			where TRC.ObjectType = OT.id and TRC.Permissions = PM.id and
			TRC.ObjectName not in (select name from model.sys.objects union select name from model.sys.all_views) and
			TRC.EventClass = 114
			group by TRC.ApplicationName,TRC.LoginName,TRC.ObjectName,OT.Description,PM.description,TRC.Success
			union
			select @NextId, @DatabaseName, @TS, 'Data Definition Language',TRC.ApplicationName,TRC.LoginName,TRC.ObjectName,OT.Description,ES.Description,TRC.Success
			from ::fn_trace_gettable(@TraceName,default) TRC, EvtSubClass ES, ObjType OT
			where TRC.ObjectType = OT.id and TRC.EventSubClass = ES.id and
			TRC.ObjectName not in (select name from model.sys.objects union select name from model.sys.all_views) and
			TRC.EventClass = 131
			group by TRC.ApplicationName,TRC.LoginName,TRC.ObjectName,OT.Description,ES.Description,TRC.Success
		
		
		SET @TraceDeleteCmd = 'del ' + @TracePath + @DatabaseName + '_SegSO_EffPerm*.trc'
		EXEC master..XP_CMDSHELL @TraceDeleteCmd, no_output
	END
	
END