DBA Scripts: Get Login and Job Inventory

April 26, 2026

–

Part of the DBA-Tools Project.

Two Plain Inventories Every Migration Plan Needs

Permissions and Role Membership and SQL Agent and Jobs already cover the deep-dive on access and job health. These two scripts are the plain, focused inventory underneath both: Get-LoginInventory lists every server login by type and status, no permission detail, just what exists. Get-JobInventory lists every SQL Agent job with its owner, no schedule or run-history detail, just what’s there.

Both exist for the same reason: a migration or login-script generator needs a clean list to work from, not a full security or health audit.

Why Login and Job Inventory Matter

A migration plan starts with knowing exactly what logins and jobs need to exist on the target server, this is the checklist, not the analysis
A disabled login or job sitting in the list is worth a second look, whether it’s safe to leave behind or needs to move too
Job ownership under a specific login (rather than sa or a service account) is a common migration gap, if that login doesn’t move, the job’s EXECUTE AS context breaks silently on the new server
Both scripts are read-only inventories on purpose, deliberately not overlapping with the deeper permission or job-health scripts, so you can pull a clean list without wading through analysis you don’t need yet

When to Run These Scripts

Building a migration runbook or login/job creation script for a target server
Confirming exactly what exists before generating DDL with the Generate-* migration scripts
Reviewing a server you’ve just inherited, for a fast first-pass list before the deeper Permissions and Role Membership or SQL Agent and Jobs reviews
Alongside the rest of the Server Inventory cluster when planning a migration

The Scripts

1. Get-LoginInventory — Every Server Login, by Type and Status

/*
Script Name : Get-LoginInventory
Category    : migration
Purpose     : Inventory server logins by type and status for migration and access review.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-login-and-job-inventory/)
Requires    : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-login-and-job-inventory/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
    sp.name AS login_name,
    sp.type_desc AS login_type,
    CASE WHEN sp.is_disabled = 1 THEN 'Disabled' ELSE 'Enabled' END AS status,
    sp.default_database_name,
    sp.create_date,
    sp.modify_date
FROM sys.server_principals AS sp
WHERE sp.type IN ('S', 'U', 'G')
  AND sp.name NOT LIKE '##%'
  AND sp.name NOT LIKE 'NT AUTHORITY%'
  AND sp.name NOT LIKE 'NT SERVICE%'
ORDER BY sp.type_desc, sp.name;

2. Get-JobInventory — Every SQL Agent Job, with Owner

/*
Script Name : Get-JobInventory
Category    : migration
Purpose     : Inventory SQL Agent jobs with owner for migration dependency checks.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-login-and-job-inventory/)
Requires    : db_datareader on msdb
*/
-- Blog: https://sqldba.blog/dba-scripts-get-login-and-job-inventory/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS status,
    j.description,
    j.date_created,
    j.date_modified,
    ISNULL(sp.name, '(unknown)') AS owner_name,
    j.job_id
FROM msdb.dbo.sysjobs AS j
LEFT JOIN sys.server_principals AS sp
    ON j.owner_sid = sp.sid
ORDER BY j.name;

How To Run From The Repo

Clone DBA Tools, initialize and run whichever script answers your question:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Every server login, by type and status:
.\run.ps1 Get-LoginInventory

# Every SQL Agent job, with owner:
.\run.ps1 Get-JobInventory

# Either against a remote sql server:
.\run.ps1 Get-JobInventory -ServerInstance SQLSERVER01

These scripts live in the repo at:

Example Output

Real output from this lab instance, not staged.

Get-LoginInventory (11 logins, condensed here):

login_name	login_type	status
AppSupport_User	SQL_LOGIN	Enabled
DemoDisabledAdmin	SQL_LOGIN	Disabled
sa	SQL_LOGIN	Enabled
HPAI01\Peter	WINDOWS_LOGIN	Enabled

Get-JobInventory (5 jobs, all owned by sa):

job_name	status	owner_name
DBA Daily Full Backups	Enabled	sa
DBA Index Stats Maintenance	Enabled	sa
DBA Watchtower CollectDatabaseGrowthEvents	Enabled	sa
syspolicy_purge_history	Enabled	sa

Every job on this lab instance is owned by sa, a real, valid finding, since sa migrates trivially to any target server, this instance has no job-ownership migration risk to plan around.

Understanding the Results

status = Disabled — worth a specific decision, not an automatic skip, confirm whether the login/job needs to move to the target too or is genuinely retired
owner_name for a job — if it’s a named individual login rather than sa or a service account, confirm that specific login is included in the migration plan; otherwise the job’s execution context breaks on the target
default_database_name — check this exists on the target server too, a login whose default database doesn’t exist yet fails to connect until fixed
Two clean, focused lists — neither script tells you what a login can do or whether a job is healthy, that’s Permissions and Role Membership and SQL Agent and Jobs; these are the starting checklist, not the analysis

Best Practices

Pull both lists early in any migration plan, before the deeper permission or job-health review, so you know the full scope of what needs to move
Cross-check every non-sa, non-service-account job owner against the login list to confirm it’s included in the plan
Re-run after the migration on the target server and diff against the source list to confirm nothing was silently missed
Use alongside the Generate-* scripts for actually creating the logins and jobs on the target

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

How is this different from Permissions and Role Membership?

Permissions and Role Membership answers “what can this login actually do,” explicit grants, role memberships, server roles. Get-LoginInventory just answers “what logins exist,” a plain checklist, useful specifically when you need a source list to build a migration script from, not an access audit.

Why does Get-JobInventory show “(unknown)” for some job owners?

A job’s owner_sid no longer matching any current server login, usually because the original owner login was dropped after the job was created. Worth investigating and reassigning to a current login before it becomes a real problem, an unowned job can fail unexpectedly depending on how SQL Agent resolves execution context.

Summary

Before a migration plan gets into permissions and job health, it needs a plain, complete list of what exists. These two scripts are exactly that, logins by type and status, jobs by owner, no analysis, just the checklist a migration script generator or runbook can work from directly.

Pull both early in any migration plan, and re-run on the target afterward to confirm the diff against the source is exactly what you expected.

Inventory Migration

Blog Categories

Comments

Leave a Reply Cancel reply