[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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