How to findout who has modified the Stored procedures recently

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to findout who has modified the Stored...

How to findout who has modified the Stored procedures recently

Rate Topic

Display Mode

Topic Options

Author

Message

sram24_mca

Posted Thursday, December 04, 2008 12:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 3:01 AM
Points: 83, Visits: 320

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

Post #613439

Jack Corbett

Posted Thursday, December 04, 2008 8:32 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #613742

Shabba

Posted Thursday, December 04, 2008 8:40 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:08 AM
Points: 304, Visits: 1,726

You could always ping the hostname to get the ip address, once you've run Jack's script.

Post #613753

curious_sqldba

Posted Thursday, December 04, 2008 9:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 1,171, Visits: 2,684

but this doesnt gives the name of the store proc modified...

Post #613847

Jack Corbett

Posted Thursday, December 04, 2008 9:39 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #613858

Sam Greene

Posted Thursday, December 04, 2008 5:11 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:36 PM
Points: 253, Visits: 492

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
*/

Post #614198

nick harris-294131

Posted Thursday, December 04, 2008 7:58 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, January 24, 2013 3:57 PM
Points: 51, Visits: 160

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:

Post #614230

« Prev Topic | Next Topic »

Permissions