I'm trying to go through our development DB right now and clean up some of the old test procs/tables. Is it possible to determine what user created objects in a SQL Server 2005 database? If so, how would I go about finding that information?
Edit: Just wanted to clarify that the objects in question already exist. Setting up auditing and triggers probably wouldn't do me much good. I guess I was mostly looking for a way to use the system tables/views to get to the information.
SQL Server audit http://www.sqlmag.com/Articles/ArticleID/98709/pg/1/1.html
If you need a small and specific mechanism, you can search for DLL Triggers info.
The answer is "no, you probably can't".
While there is stuff in there that might say who created a given object, there are a lot of "ifs" behind them. A quick (and not necessarily complete) review:
sys.objects (and thus sys.tables, sys.procedures, sys.views, etc.) has column principal_id. This value is a foreign key that relates to the list of database users, which in turn can be joined with the list of SQL (instance) logins. (All of this info can be found in further system views.)
But.
A quick check on our setup here and a cursory review of BOL indicates that this value is only set (i.e. not null) if it is "different from the schema owner". In our development system, and we've got dbo + two other schemas, everything comes up as NULL. This is probably because everyone has dbo rights within these databases.
This is using NT authentication. SQL authentication probably works much the same. Also, does everyone have and use a unique login, or are they shared? If you have employee turnover and domain (or SQL) logins get dropped, once again the data may not be there or may be incomplete.
You can look this data over (select * from sys.objects), but if principal_id is null, you are probably out of luck.
If each user has its own sqlserver login you could try this
select so.name, su.name, so.crdate
from sysobjects so join sysusers su
on so.uid = su.uid
order by so.crdate