Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to findout who has modified the Stored procedures recently


How to findout who has modified the Stored procedures recently

Author
Message
sram24_mca
sram24_mca
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 505
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11018 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Shabba
Shabba
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 1846
You could always ping the hostname to get the ip address, once you've run Jack's script.
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
but this doesnt gives the name of the store proc modified...
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11018 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Sam Greene
Sam Greene
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 584
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
*/
nick harris-294131
nick harris-294131
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 182
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:
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search