September 2, 2008 at 10:30 pm
Is there a way I can display person who created/last modied table. In this case, all tables have ower dbo. and I require login ID/User ID/User Name who created the tables. Thanks
I am using SQL 2005 server.
Below SQL will only give ower not the one who created the tale
Select * From Information_Schema.tables
September 3, 2008 at 2:36 am
I don't know anywhere where the user name is stored. You can create a DDL trigger that can track that event. You also can check the default trace that is running in SQL Server 2005 unless someone stopped and/or removed it. You can query sys.traces to find the location of the file and you can open it in profiler or you can open it using fn_trace_gettable([filepath], [no of files]). If you wait too long you may miss it as the files rollover at 5 20MB files. Like this:
Select
TT.*
From
sys.traces T Cross Apply
fn_trace_gettable(T.path, 5) TT Join
msdb.sys.trace_events TE On
TT.EventClass = TE.trace_event_id
Where
T.id = 1 And
TE.name = 'Object:Altered'
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
September 3, 2008 at 6:57 am
It only goes back as far as the last time the server was restarted, but you can track schema changes through the built in report "Schema Changes History." I ran it and saw that it's pulling data from a trace file (must be a black box of some sort on the machine). I think if you did the same thing, you could get what you needed, but there won't be a lot of history there. Here's the query it ran:
begin
declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
declare @temp_trace table (
obj_name nvarchar(256)
, obj_id int
, database_name nvarchar(256)
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256)
, login_name nvarchar(256)
, user_name nvarchar(256)
, application_name nvarchar(256)
, ddl_operation nvarchar(40)
);
select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX(''%\%'', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';
insert into @temp_trace
select ObjectName
, ObjectID
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
, ''temp''
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = db_id() ;
update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46;
update @temp_trace set ddl_operation = ''DROP'' where event_class = 47;
update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164;
select @d1 = min(start_time) from @temp_trace
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24;
select @diff as difference
, @d1 as date
, object_type as obj_type_desc
, (dense_rank() over (order by obj_name,object_type ) )%2 as l1
, (dense_rank() over (order by obj_name,object_type,start_time ))%2 as l2
, *
from @temp_trace where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise
order by start_time desc;
end else
begin
Select top 0 1 as difference, 1 as date, 1 as obj_type_desc, 1 as l1, 1 as l2, 1 as obj_name, 1 as obj_id, 1 as database_name, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as user_name, 1 as application_name, 1 as ddl_operation
end
end try
begin catch
select -100 as difference
, ERROR_NUMBER() as date
, ERROR_SEVERITY() as obj_type_desc
, 1 as l1, 1 as l2
, ERROR_STATE() as obj_name
, 1 as obj_id
, ERROR_MESSAGE() as database_name
, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as user_name, 1 as application_name, 1 as ddl_operation
end catch',@params=N''
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2008 at 5:02 pm
Thanks guys. Much appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply