I work on a team of about 10 people and we're hooked up to the same development database. Theres not just one 'database guy'; we all pretty much create and update tables as we need them.
Sometimes I'd like to know who created a certain table, and I'd like an easier way to find out than sending an email out to all 10 of us asking who did it.
Does SQL server record the userID of the person who created a table or procedure? Ive looked into the sysobjects table, and it has crdate (create date) which is handy, but I dont see distinct user ids. Maybe in another table somewhere?
Also it would be cool if it could not only list the new objects created, but also any object that has been modified, for eg if someone added a column to an existing table.
If this is SQL Server 2005, you can set up a DDL trigger for the CREATE TABLE statement to populate a log table. Of course, there would be no guarantee someone would not delete the log table, but you have to trust your coworkers to some extent.
Were using 2008.
No one would delete the log table. Theyre not trying to *hide* what they do, they just dont really announce when they do stuff.
Would I also have to create a Trigger for ALTER TABLE, DROP TABLE and same for Procedures as well? I didnt know you could do that, so thats cool.
But what information do I have access to inside the trigger? Does the username of the person who fired the trigger get passed in or something?
Also, I just noticed in the sysobjects table, there is a column called UID. This sounds like it would be useful except ALL the values are 1. I think thats because we all log in with the same master username and password. This is a problem isnt it? There is no way I can distinguish between actual users, is there?
sysobjects is a "compatibility" view. It is kept for backward compatibility to SQL 2000. sys.objects (or sys.tables) is the more "modern" catalog, but the safest is information_schema.tables, as that is some sort of industry standard schema, and should not change from version to version, except with large warning labels. You can fool around with the suser_sname() function to retrieve the NT name of the logged in user. Other information can be found here: CREATE TRIGGER (Transact-SQL).
EDIT: Oh, and UID in sysobjects is the owner's uid. uid = 1 is dbo.