I'm not even sure SQL Server stores this kind of information, but, is it possible to get the username of the person who last modified a particular stored procedure, function, table or view?
Nothing critical, just wondering. Thanks!
views:
68answers:
3
+6
Q:
Is it possible to get the user who last edited a stored proc, function, table or view in SQL Server?
+1
A:
It doesn't store this information out of the box.
You can use SQL Trace and Event notification (see the corresponding MSDN Article) to log this kind of information by yourself.
I have no experience with these technologies though ...
MartinStettner
2010-01-18 14:39:11
+1
A:
If you are using SQL Server 2008, you could use some new features that allow you to put triggers on DDL changes. You can then track, based on the authenticated user, who made the change.
I think these triggers are new to SQL 2008, but they may be available in 2005.
Having said this, ideally you should have your database schema under source control, using a tool like Visual Studio Database Professional. Then you'd have a complete history of who did what and when.
Randy
Randy Minder
2010-01-18 14:45:55
Interesting you mention source control... This is kind of why I'm investigating this. We currently have the ability to store stored procedures, table definitions, etc. in source control automatically, but no capability for tracking who actually made the change (since commits are done on a periodic basis). I'd like to stay away from paid solutions as far as DB source control goes, and roll our own, so to speak.
Pwninstein
2010-01-18 15:34:19
Visual Studio Database Professional is free if you are using Visual Studio Team Suite or Visual Studio Development Edition.
Randy Minder
2010-01-18 18:02:41
I see - unfortunately, I believe we're all using VS 2008 (soon to be 2010) Professional (not Team System). That's good to know, though. Thanks!
Pwninstein
2010-01-18 18:36:56
you need not have VSDB professional for storing DB schemas. They are SQL statements and any source control will do. SVN is free :)
ram
2010-01-21 02:10:49
@Ram - I realize that storing SQL statements will do (which is what we do currently), and we do use SVN (which is great). The problem is, we want to automate the commit process (which is near trivial) but we want to still track WHO actually made the change being committed. Thanks!
Pwninstein
2010-01-21 03:49:10
+1
A:
Definitely put DDL triggers in place. Even if you don't end up using them, or if you end up putting a decent source control system in place, still have the DDL triggers in place so that you can be sure about what's going on.
Rob Farley
2010-01-21 01:40:35