Determine what user created objects in SQL Server

Question

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

User · Answer

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

User · Answer

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

User · Answer

If you need a small and specific mechanism  you can search for DLL Triggers info

User · Answer

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

[sql] Determine what user created objects in SQL Server

Examples related to sql

Examples related to sql-server-2005

Examples related to tsql