Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to findout who has modified the Stored procedures recently Expand / Collapse
Author
Message
Posted Thursday, December 4, 2008 12:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:03 AM
Points: 90, Visits: 386
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
Posted Thursday, December 4, 2008 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 11,148, Visits: 12,889
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
Posted Thursday, December 4, 2008 8:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:33 AM
Points: 307, Visits: 1,846
You could always ping the hostname to get the ip address, once you've run Jack's script.
Post #613753
Posted Thursday, December 4, 2008 9:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 1,274, Visits: 2,936
but this doesnt gives the name of the store proc modified...
Post #613847
Posted Thursday, December 4, 2008 9:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 11,148, Visits: 12,889
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

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 #613858
Posted Thursday, December 4, 2008 5:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:41 PM
Points: 253, Visits: 539
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
Posted Thursday, December 4, 2008 7:58 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:13 PM
Points: 51, Visits: 163
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 »

Add to briefcase

Permissions Expand / Collapse