How can I track which user has last modified the stored proc in sql server 2005+?

Question

Inspired by this question How can I quickly identify most recently modified stored procedures in SQL Server I am wondering if at the same time we can identify the user? How to do this using sql program(if any possible)? I am eager to know.

Thanks in advance

Answer 1

I do not believe that this is possible after the fact. This is very similar to this question Microsoft SQL Server - Who created a Stored Procedure?

Answer 2

If the stored procs were stored in a version control system then you would have complete access to history records of who checked out that stored proc, what modifications were done, and when the file was commited back in to the repository. Granted this is only for the file itself, and not for when the stored proc would have been modififed on the SQL server itself.

Answer 3

We have all our Sprocs in separate files (with version control etc.) - rather than just RightClick and Edit directly in the database!

We have this at the top of every SProc script

EXEC dbo.MyLogScriptRun 'MySProcName', '091113'

this creates an entry in a script logging table that MySproc was created and its version is 091113. We also log the currently logged on user, plus database and server name (which helps when a database is restored to another server and we wonder how MySproc got to be THAT version on THIS server!)

We also use this to check what version is installed on a specific client DB e.g. when diagnosing a problem, and as the basis of knowing what starting version to use (from version control) to deploy a fix; also for deplying an upgrade (so we can work out what SProcs are newer and need to be incorporated into a "release package")

Most of this you could do with DDL triggers (in SQL2005 onwards) but I'm not sure about being able to also store a version number.

We use this to log other scripts we run too - DDL manipulation scripts run during updates and so on.

The logging sproc is written so that executing

EXEC dbo.MyLogScriptRun 'MySProcName'

provides a list from the log. (i.e. I can highlight the first part of the line, excluding the version number, to get a list, or execute the whole line to create a log entry).

Answer 4

If you're talking about "what is being used in the prod DB", rather than "in source control", then no.

You'd need SQL Server 2005 and above and DDL triggers.

Answer 5

Two options:

Try to run the SQL Server Management Studio report "Schema Changes History". Right-click "Database". Select "Standard Reports". Select "Schema Changes History".

If this does not work and you are really desparate: Try "::fn_dblog". Example: SELECT * FROM ::fn_dblog(NULL, NULL) This will let you look at the transaction log directly.

Answer 6

You can use SQL Server Profiler to track DDL actions. In Event Selection, check the following events:

Objects / Object: Altered

Objects / Object: Created

Objects / Object: Deleted

There are also lots of customization options: you can save the output to a file or table, filter the output furthermore based on any columns etc. etc.