December 4, 2008 at 12:25 am
Dear all,
How to find out who has modified the stored procedures recently in sqlserver 2005 with IPADDRESS,Login name and Client machine name
Thanks,
Ram
December 4, 2008 at 8:32 am
This doesn't get you IP Address, but it will get you the rest:
SELECT
I.*
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(T.path, T.max_files) I Join
sys.trace_events E On
I.eventclass = E.trace_event_id
Where
T.id = 1 And
E.name = 'Object:Altered'
This is based on the Default trace that is installed and started when SQL Server starts. It created a maximum of 5 20 MB trace files and rolls them over so when the 5th is full it deletes the oldest and creates a new file. It also creates a new file whenever the SQL Server is restarted. If you need to keep more files you can create a process that archives them or puts the data in a table.
I don't know of a way to get the IP Address.
Another option is create a DDL trigger that logs information.
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
December 4, 2008 at 8:40 am
You could always ping the hostname to get the ip address, once you've run Jack's script.
December 4, 2008 at 9:32 am
but this doesnt gives the name of the store proc modified...
December 4, 2008 at 9:39 am
It does provide the db_id and the object_id and you can use that information to get that information. YOu would need to execute the query in the user database.
Other than creating a DDL trigger I don't know of any other way to get this information.
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
December 4, 2008 at 5:11 pm
Here is some script I put together from a few sources that will create triggers on all your dbs, create an audit database and setup some some permissions. It may or may not work, use at your own risk.
CREATE DATABASE DBA_AUDIT
GO
USE DBA_AUDIT
GO
CREATE TABLE AuditLog
(ID INT PRIMARY KEY IDENTITY(1,1),
Command NVARCHAR(1000),
PostTime DATETIME,
HostName NVARCHAR(100),
LoginName NVARCHAR(100),
DatabaseName NVARCHAR(100)
)
GO
CREATE ROLE AUDITROLE
GO
sp_adduser 'guest','guest','AUDITROLE'
GO
GRANT INSERT ON SCHEMA::[dbo]
TO AUDITROLE
--CREATE TRIGGER IN ALL NON SYSTEM DATABASES
DECLARE @dataname varchar(255),
@dataname_header varchar(255),
@command VARCHAR(2000),
@usecommand VARCHAR(100)
--SET @command = '';
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sys.databases
WHERE name not in ('master', 'pubs', 'tempdb', 'model','msdb')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status = 0)
BEGIN
PRINT '----------BEGIN---------'
--PRINT 'DATANAME variable: ' + @dataname;
--EXEC ('USE ' + @dataname);
--PRINT 'CURRENT db: ' + db_name();
SET @command = 'DECLARE @my_cmd NVARCHAR(2000); SET @my_cmd = ''CREATE TRIGGER DBA_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
DECLARE @dbname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''', ''''NVARCHAR(1000)'''')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'''''''','''''''')))
SET @posttime = @data.value(''''(/EVENT_INSTANCE/PostTime)[1]'''', ''''DATETIME'''')
SET @spid = @data.value(''''(/EVENT_INSTANCE/SPID)[1]'''', ''''nvarchar(6)'''')
SET @loginname = @data.value(''''(/EVENT_INSTANCE/LoginName)[1]'''',
''''NVARCHAR(100)'''')
SET @hostname = HOST_NAME()
SET @dbname = @data.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''NVARCHAR(100)'''')
INSERT INTO [DBA_AUDIT].dbo.AuditLog(Command, PostTime,HostName,LoginName,DatabaseName)
VALUES(@cmd, @posttime, @hostname, @loginname, @dbname);''; EXEC [' + @dataname + ']..sp_executesql @my_cmd'
PRINT @command
EXEC (@command);
FETCH NEXT FROM datanames_cursor INTO @dataname;
PRINT '----------END---------'
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor
/*THIS SECTION REMOVES AUDITING TRIGGER*/
--sp_msforeachdb @command1='USE [?]; DROP TRIGGER [DBA_Audit] ON DATABASE'
/*THIS SECTION TOTALLY REMOVES AUDITING*/
--DROP DATABASE [DBA_AUDIT]
--DROP TABLE AUDIT_LOG
/* THIS TESTS THE AUDITING
USE [SQL_DBA]
EXECUTE AS LOGIN = 'SQL_DBA_REPORTS'
CREATE TABLE TEST1
(ID INT PRIMARY KEY IDENTITY(1,1),
Command NVARCHAR(1000),
PostTime DATETIME,
HostName NVARCHAR(100),
LoginName NVARCHAR(100),
DatabaseName NVARCHAR(100)
)
GO
*/
December 4, 2008 at 7:58 pm
You can run a server side trace to look for DDL commands.
Here is my script that creates a new trace file. I have it set up in a job that runs at midnight or so. I then have an ssis package to grab the new file and import it into a table so I always have record of all DDL changes.
(I forgot where I got this idea but I did get it from some other wiser soul.)
declare @stop_time datetime
declare @filename NVARCHAR(245)
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
set @stop_time = Cast(convert(varchar,getdate(),101) + ' 11:59:59 PM' as Datetime)
set @filename = '\\servername\share\DDL_AUDIT_Trace_' + CONVERT(VARCHAR,getdate(),110)
exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize, @stop_time
if (@rc != 0) goto error
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 46, 8, @on
exec sp_trace_setevent @TraceID, 46, 12, @on
exec sp_trace_setevent @TraceID, 46, 28, @on
exec sp_trace_setevent @TraceID, 46, 10, @on
exec sp_trace_setevent @TraceID, 46, 14, @on
exec sp_trace_setevent @TraceID, 46, 34, @on
exec sp_trace_setevent @TraceID, 46, 3, @on
exec sp_trace_setevent @TraceID, 46, 11, @on
exec sp_trace_setevent @TraceID, 47, 8, @on
exec sp_trace_setevent @TraceID, 47, 12, @on
exec sp_trace_setevent @TraceID, 47, 28, @on
exec sp_trace_setevent @TraceID, 47, 10, @on
exec sp_trace_setevent @TraceID, 47, 14, @on
exec sp_trace_setevent @TraceID, 47, 34, @on
exec sp_trace_setevent @TraceID, 47, 3, @on
exec sp_trace_setevent @TraceID, 47, 11, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'alter %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'create %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'drop %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'truncate %'
set @intfilter = 2
exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 92861e4c-357b-4663-8f81-f4c7b91f26fa'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - a5f1c518-15c2-4e17-8a2d-4ec1e5f93fc0'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 6230405d-9f00-47e4-9ee3-4d84dd4ef409'
exec sp_trace_setfilter @TraceID, 34, 0, 7, N'%#%'
exec sp_trace_setstatus @TraceID, 1
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply