February 18, 2009 at 11:36 am
Mike Levan (2/18/2009)
JackIf you want notifications you can do the DDL triggers or you could do a job that queries the default trace on a regular basis which sends you an email when a procedure is altered.
This is interesting, how could you do this.
thanks
Roy supplies a solution here
Roy Ernest (2/18/2009)
If you are using SQL 2005 (I assume you are) you can look at DDL Triggers.CREATE TRIGGER AuditProc
ON DATABASE
FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
DECLARE @event XML
SET @event = EVENTDATA()
INSERT INTO AuditTable (ChangeTime, , ObjectDesc, LoginID)
VALUES
(
GetDate(),
@event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@event.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
Hope this helps
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 18, 2009 at 11:42 am
Mike Levan (2/18/2009)
JackIf you want notifications you can do the DDL triggers or you could do a job that queries the default trace on a regular basis which sends you an email when a procedure is altered.
This is interesting, how could you do this.
thanks
First a disclaimer that I have never actually done this, I am tossing out ideas that should be able to be done. What I am posting here should be tested and refined, especially if you plan on putting on a production server. So here is something you could put in a job:
IF EXISTS (SELECT 1 FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1,
CHARINDEX('_', T.[path]) - 1) +
'.trc'
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON I.EventClass = TE.trace_event_id
WHERE
T.id = 1 And
I.DatabaseName = '[your database name]' AND
-- you can use the events
TE.[name] IN ('Object:Altered', 'Object:Deleted', 'Object:Created') AND
I.StartTime >= DATEADD(minute, -15, GETDATE())*/)
BEGIN
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT
TE.[name] AS event_name,
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
I.StartTime,
I.ObjectID,
I.ObjectName,
I.ObjectType,
S.principal_id,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN CHARINDEX(''_'', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1,
CHARINDEX(''_'', T.[path]) - 1) +
''.trc''
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON I.EventClass = TE.trace_event_id LEFT JOIN
sys.server_principals S
ON CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 AND
I.StartTime >= DATEADD(minute, -15, GETDATE()) And
I.DatabaseName = ''[your database name]'' AND
-- you can use the events
TE.[name] IN (''Object:Altered'', ''Object:Deleted'', ''Object:Created'')
'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'youremail@yourcompany.com', -- varchar(max)
@subject = N'Altered Objects', -- nvarchar(255)
@query = @query, -- nvarchar(max)
@execute_query_database = 'master',
@attach_query_result_as_file = 1, -- bit
@query_attachment_filename = N'AlteredObjects.txt'
END
I'd change the -15 in the DateAdd based on how often you schedule the job to run. I don't think I'd run it more than every 15 minutes though. Some of it would have to be based on activity on the server. If your files are rolling over less you can do it less often and you could restrict the query to only access the most current file which would reduce impact.
Again, I don't have this running anywhere, but in theory it would work and I will put it on my dev server this afternoon.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 2:28 pm
only events i am interested are
1. Add Login to DB and type of role assigned
2. Delete login in DB or dropped login from the memberrole
February 18, 2009 at 2:44 pm
Mike Levan (2/18/2009)
only events i am interested are1. Add Login to DB and type of role assigned
2. Delete login in DB or dropped login from the memberrole
Do you mean User or Login? User is permission to access a database while a Login is permission to connect to the SQL Server.
If you mean user you would want to query for:
Audit Add Member to DB Role Event
These are fired whenever a User is created or deleted and whenever a User is added to or removed from a Database role. Both of these events are included in the Default trace.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 2:50 pm
Jack Corbett (2/18/2009)
you could restrict the query to only access the most current file which would reduce impact.
filter out tempdb objects too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2009 at 1:37 pm
thank you for the help, gonna go try a few of these ideas
[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply