Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Query

Hi,

I need to know who updated the SProc and SQL Job last month.
Thanks
0
SanPrg
Asked:
SanPrg
  • 3
  • 2
1 Solution
 
Tony303Commented:
For the SProc question.


In SSMS, right click on DB (or Server), choose Reports / Standard Reports / Schema Changes History.

Hopefully the data goes back far enough to last month.

T
0
 
Rajkumar GsSoftware EngineerCommented:
Hi SanPrg

Finding the list of Stored Procedures modified and created for last x days is also possible using sql query
-- Stored Procedures MODIFIED within 7 days 
SELECT name
 FROM sys.objects
 WHERE type = 'P'
 AND DATEDIFF(D,modify_date, GETDATE()) < 7

-- Stored Procedures CREATED within 7 days 
SELECT name
 FROM sys.objects
 WHERE type = 'P'
 AND DATEDIFF(D,create_date, GETDATE()) < 7

Open in new window


Raj
0
 
Rajkumar GsSoftware EngineerCommented:
To get the list of Jobs modified in last 100 days, use the below query

select s.name,l.name, s.date_created, s.date_modified
 from  msdb..sysjobs s 
 left join master.sys.syslogins l on s.owner_sid = l.sid
 where DATEDIFF(D, s.date_modified, GETDATE()) < 100

Open in new window


Raj
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SanPrgAuthor Commented:
Hi Raj,
So far it's good but I need to know who modified SProc and Jobs.
0
 
Rajkumar GsSoftware EngineerCommented:
Hi

For Jobs, not sure this is the modified user
select s.name,l.name user_modified, s.date_modified
 from  msdb..sysjobs s 
 left join master.sys.syslogins l on s.owner_sid = l.sid
 where DATEDIFF(D, s.date_modified, GETDATE()) < 100

Open in new window

0
 
SanPrgAuthor Commented:
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now