June 27, 2011 at 3:15 am
Good day all
Is it possible to track changes which were made to the db's. Why I ask I've seen few storedprocs and tables missing from the master db.(I know no access should be given to the master db and..... But I just started here so hopefully will implement best sql practices as time goes on). Could someone please assist.
June 27, 2011 at 3:27 am
Yes it is possible , depends on what you want to track :-
Database objects can be tracked by using a server tigger for DDL keywords such as create , drop alter
http://technet.microsoft.com/en-us/library/ms189799.aspx
Data level changes are more difficult to track u could create a timestamp column and check for changes to timestamps on each row , however this might not be practical for everybody.
Other options include running traces etc .
However the most important thing to do is make sure only those authorized to make changes are ever allowed to. the rest are just after thoughts
June 27, 2011 at 3:29 am
Unfortunately there's no builtin tracking system in SQL2005.
You can set up a DDL trigger and log changes to a table.
Something similar to this:
CREATE TRIGGER [TR_Auditing] ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
DECLARE
@DatabaseName varchar(256),
@EventType varchar(50),
@ObjectName varchar(256),
@ObjectType varchar(25),
@TSQLCommand varchar(max),
@LoginName varchar(256)
SELECT
@DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@TSQLCommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
INSERT INTO changelog (
databasename,
eventtype,
objectname,
objecttype,
sqlcommand,
loginname
)
VALUES (
@DatabaseName,
@EventType,
@ObjectName,
@ObjectType,
@TSQLCommand,
@LoginName
)
END
Hope this helps
Gianluca
-- Gianluca Sartori
June 27, 2011 at 3:35 am
Thanks a lot will try to implement this. So I guess I wouldn't be able to see what happend in the past few day but this will assist me in the future.
Thanks again appreciate the help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply