SQL Server – How to Prevent Jobs and Reports from Stopping When Deleting Inactive Users

jobssql servert-sql

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?

Best Answer

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

NUTS

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.

Related Question