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.
This question is related to
sql
sql-server-2005
tsql
The answer is
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 the object was recently created, you can check the Schema Changes History report, within the SQL Server Management Studio, which "provides a history of all committed DDL statement executions within the Database recorded by the default trace":
You then can search for the create statements of the objects. Among all the information displayed, there is the login name of whom executed the DDL statement.
If you need a small and specific mechanism, you can search for DLL Triggers info.
If each user has its own SQL Server 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
Source: Stackoverflow.com