Add loginName...etc on select * from sys.procedures

Question

Add loginName...etc on select * from sys.procedures

Tac11

SSCertifiable

Points: 6976
More actions
January 6, 2017 at 1:44 pm

#333373

Hi expert,
How to add LoginName, ApplicationName, HostName on select * from sys.procedures to find out who altered the Store procedure. I tried from default trace but not working it' doesn't hold old data.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Tac11 (1/6/2017)
Hi expert,
How to add LoginName, ApplicationName, HostName on select * from sys.procedures to find out who altered the Store procedure. I tried from default trace but not working it' doesn't hold old data.
Thanks

SQL Server doesn't store that type of information for very long. You could possibly glean it from session DMVs but probably not worth doing so.

I believe the recommendation will be that you need to setup some form of DDL auditing using DDL triggers. Not much you can do about things that have occurred in the past, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 2

The information does exist in the default trace - the schema change history report will show you the information you are looking for...to get the code open Profiler and have it running when you execute the report and you can capture the code they use.

The reports were published at one time but I don't have a link to where you can get them now.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

Something I've setup before is using Event Notification to capture DDL activity:

https://www.mssqltips.com/sqlservertip/2121/event-notifications-in-sql-server-for-tracking-changes/

I've found it works very well, and you can store the captured events in a separate database that has Service Broker enabled, it doesn't need to be in the database where the changes happen. There's a wide variety of things it can signal an event on:

https://msdn.microsoft.com/en-us/library/bb522542.aspx