HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Delete inactive users - how to prevent jobs and reports to stop working

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
reportspreventdeleteandworkingstopjobshowinactiveusers

Problem

We have several Microsoft SQL servers in our organization and the previous DBA have not performed user reviews on any of them, so some servers are filled with inactive local users. I have a script that looks at their last logon date:

SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY [Login] ASC;


But how can I create a similar script that lists all jobs and reports made by the user, so that they won't stop working when and if I delete the inactive users? Are there any best practices when it comes to deleting users from Microsoft SQL Servers?

Solution

The company I work for writes a free script called sp_Blitz that will warn you when Agent jobs are owned by users.

It'll warn you about a whole bunch of other stuff too, and is a pretty decent way to give your SQL Servers a free health check.

Context

StackExchange Database Administrators Q#188301, answer score: 4

Revisions (0)

No revisions yet.