Menu
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.
782
We can use SSMS in-built object search functionality to find out specific objects across all online databases in SQL instance. Navigate to View-> Object Explorer Details in SSMS. You can use a keyboard shortcut F7 to open it.
select * from sys. databases.. you can find . bak files location and all databases owner_sid and you can find by Right click SQL Server Instance and Select Reports -> Standard Reports -> Schema Changes History mytechmantra.com/LearnSQLServer/… The only place where this information is written is in the transaction log.
To run the Schema Changes History report open SQL Server Management Studio, make a right click on an object (the instance name or a database name, for example), then select "Reports", click on "Standard Reports", and then click on "Schema Changes History" report. The result is the following report.
Join with sys. database_principals on principal_id for the username! That's the PRINCIPAL that created THE SCHEMA, in other words, dbo, you ...
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.
93
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.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
