CODEWITHSUNDEEP
sql-server-2008history
Mark Carpenter
Mark Carpenter

Reputation: 17775

Is it possible to get the user who last edited a stored proc, function, table or view in SQL Server?

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!

Upvotes: 6

Views: 191

Answers (3)

Rob Farley
Rob Farley

Reputation: 15849

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.

Upvotes: 1

MartinStettner
MartinStettner

Reputation: 29174

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 ...

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48522

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

Upvotes: 1

Related Questions