get user who altered stored procedure from server

First I create a table for logging purpose.

CREATE table Logtable
(username varchar(2000), Modify_Date datetime, ProcName varchar(2000))

Then I create the Trigger

CREATE TRIGGER safety
ON DATABASE
FOR ALTER_PROCEDURE
AS
declare @name varchar(200)

--look for the object with stored procedure type that has the last modify time.
set @name = (select  top 1 Name from sys.all_objects
where type_desc like 'SQL_STORED_PROCEDURE'
order by modify_date desc)

--insert the information u need to log table
INSERT INTO Logtable (UserName, Modify_Date, ProcName)
select suser_name(), getdate(), @name

create  a dummy proc for testing and alter it for testing the trigger

create procedure JustAProc
as
select 'JustAProc'
go

ALTER procedure JustAProc
AS BEGIN
PRINT 'X'
END

see the content of the log

select * from Logtable

username	Modify_Date	ProcName
Melissa	3/24/10 8:17 PM	JustAProc

and just drop it after you finish play with it

DROP TRIGGER safety
ON DATABASE;
go
DROP PROCEDURE JustAProc
GO
Drop Table Logtable

Advertisement