How to audit SQL Server service accounts and logins: Queries to inventory, review and fix every login

Most SQL Server instances carry logins nobody can explain: SQL logins with policy checks switched off, application accounts in sysadmin, Windows logins for people who left years ago, and service accounts running as domain users with passwords set at install time. This page is the audit runbook: read-only catalog queries to inventory every server principal and service account, the checks that turn the inventory into findings, and the remediation steps with a rollback for each. It is written for DBAs, identity engineers and auditors working on SQL Server 2016 and later on Windows, with notes where SQL Server 2022 and 2025 changed permissions or behaviour. The hardening design for the engine as a whole is in the SQL Server Security Guide; this page is the task-level how-to.

This is an independent guide from NHI Mgmt Group, based on Microsoft's public SQL Server documentation on Microsoft Learn as of November 2026. Catalog views and required permissions change between versions, so confirm them against the documentation for your build.

Key takeaways

• Four sources make one inventory. sys.server_principals lists every login, sys.sql_logins adds password policy flags, sys.server_role_members and sys.server_permissions show power, and sys.dm_server_services names the accounts the services run as.
• SQL Server does not record last login. No catalog column holds a last successful logon time, so dormancy needs an audit (SUCCESSFUL_LOGIN_GROUP) running for a full business cycle before you remove anything.
• Policy flags are per login. CREATE LOGIN defaults to CHECK_POLICY = ON but CHECK_EXPIRATION = OFF, so most application SQL logins never expire. Find them with is_policy_checked, is_expiration_checked and LOGINPROPERTY(name, 'PasswordLastSetTime').
• Service accounts are Windows identities. Microsoft's guidance is to use a virtual account, MSA or gMSA, separate accounts per service, and never to grant the service account extra permissions. A domain user in Domain Admins running the engine is a finding.
• Disable before you drop. ALTER LOGIN ... DISABLE is reversible in one statement; DROP LOGIN orphans database users and loses the SID unless you recorded it.

When you need this

• Access review or audit evidence: SOX, PCI DSS or ISO/IEC 27001 reviews ask who holds sysadmin and which accounts are shared or generic. The queries below produce that list per instance.
• Before a migration or consolidation: moving databases between instances is the moment orphaned users and undocumented application logins surface.
• After an incident or leaked connection string: you need to know every place a SQL login is used, what it can reach, and when its password last changed.
• Service account clean-up: moving engine and Agent services to gMSA, or checking that a domain account used by SQL Server is not over-privileged in Active Directory. See the How to convert AD service accounts to gMSA guide.

Before you start

Run the audit with a dedicated, read-only audit login granted the permissions above, not with a sysadmin account. That way the audit itself does not need standing privilege, and its activity is easy to filter from the logs.

Step-by-step

Steps 1 to 7 are read-only. Save each result set with the instance name and date, because the comparison between runs is what shows drift.

1. Inventory every server principal.

   SELECT name, principal_id, type, type_desc, is_disabled,
          create_date, modify_date, default_database_name, credential_id
   FROM sys.server_principals
   WHERE type IN ('S','U','G','E','X','C','K')
   ORDER BY type, name;

   Type codes: S SQL login, U Windows login, G Windows group, E and X Microsoft Entra login and group, C certificate-mapped and K asymmetric key-mapped logins. Logins named ##MS_...## are internal certificate-based principals; record them but do not change them. A non-NULL credential_id means the login is mapped to a stored credential, which is itself a secret to inventory.
2. Check password policy and age on SQL logins.

   SELECT name, is_disabled, is_policy_checked, is_expiration_checked,
          LOGINPROPERTY(name, 'PasswordLastSetTime')  AS password_last_set,
          LOGINPROPERTY(name, 'DaysUntilExpiration')  AS days_until_expiry,
          LOGINPROPERTY(name, 'IsLocked')             AS is_locked,
          LOGINPROPERTY(name, 'BadPasswordCount')     AS bad_password_count,
          LOGINPROPERTY(name, 'PasswordHashAlgorithm') AS hash_algorithm
   FROM sys.sql_logins
   WHERE name NOT LIKE '##%'
   ORDER BY password_last_set;

   is_policy_checked = 0 means Windows complexity and lockout do not apply to that login. is_expiration_checked = 0 is normal for application logins, so the real control is password_last_set against your rotation standard. DaysUntilExpiration returns NULL when either check is off. A hash_algorithm of 0 or 1 points to a password set on a very old version and never changed. SQL Server 2025 introduces a new iterated hash (version byte 0x03 in password_hash), so a password reset after upgrade also moves the login to the stronger hash.
3. List membership of fixed and user-defined server roles.

   SELECT r.name AS server_role, m.name AS member, m.type_desc, m.is_disabled
   FROM sys.server_role_members AS rm
   JOIN sys.server_principals AS r ON rm.role_principal_id = r.principal_id
   JOIN sys.server_principals AS m ON rm.member_principal_id = m.principal_id
   ORDER BY r.name, m.name;

   Focus on sysadmin, securityadmin (which can grant itself further rights), serveradmin and any custom role that nests these. For a Windows group in sysadmin, expand its membership in Active Directory: the group is where the real people and service accounts hide.
4. List high-impact server permissions granted outside roles.

   SELECT p.name AS grantee, p.type_desc, sp.permission_name, sp.state_desc,
          sp.class_desc, sp.major_id
   FROM sys.server_permissions AS sp
   JOIN sys.server_principals AS p ON sp.grantee_principal_id = p.principal_id
   WHERE sp.permission_name IN ('CONTROL SERVER','ALTER ANY LOGIN',
         'IMPERSONATE ANY LOGIN','IMPERSONATE','ALTER ANY LINKED SERVER',
         'ALTER ANY CREDENTIAL','ALTER SETTINGS','UNSAFE ASSEMBLY',
         'EXTERNAL ACCESS ASSEMBLY')
   ORDER BY sp.permission_name, p.name;

   CONTROL SERVER is equivalent to sysadmin for most purposes, although it respects DENY. IMPERSONATE on a login (class SERVER_PRINCIPAL, major_id is the target login) is a privilege path: a low-privilege application login that can EXECUTE AS a sysadmin login is effectively sysadmin.
5. Identify the service accounts.

   SELECT servicename, service_account, startup_type_desc, status_desc,
          last_startup_time, is_clustered, instant_file_initialization_enabled
   FROM sys.dm_server_services;

   Classify each service_account: NT SERVICE\MSSQLSERVER or NT SERVICE\SQLSERVERAGENT (virtual accounts, the default for standalone instances), a name ending in $ (MSA or gMSA), LocalSystem or NT AUTHORITY\NETWORK SERVICE, or a plain domain user. For each domain user, check in Active Directory its group membership (Domain Admins or local Administrators is a finding), password last set, whether it is shared across instances, and the SPNs registered on it (setspn -L DOMAIN\account), because a domain user with an SPN and a human-set password is a Kerberoasting target. See the Kerberoasting and AS-REP Roasting Defence Guide and How to find service accounts with Domain Admin rights.
6. Find orphaned and stale principals. Run EXEC sp_validatelogins; to list Windows logins whose user or group no longer exists in Windows. Then, in each user database:

   SELECT dp.type_desc, dp.sid, dp.name AS user_name
   FROM sys.database_principals AS dp
   LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
   WHERE sp.sid IS NULL
     AND dp.authentication_type_desc = 'INSTANCE';

   These are database users with no matching login, usually left by a restore from another instance. Also compare Windows logins with your HR leaver list and disabled AD accounts: a login for a disabled AD account cannot authenticate but still holds grants and is easy to re-enable.
7. Inventory stored credentials used by logins, Agent and linked servers.

   SELECT name, credential_identity, create_date, modify_date FROM sys.credentials;
   
   SELECT p.name AS proxy_name, p.enabled, c.name AS credential_name,
          c.credential_identity
   FROM msdb.dbo.sysproxies AS p
   JOIN sys.credentials AS c ON p.credential_id = c.credential_id;
   
   SELECT s.name AS linked_server, l.local_principal_id, l.uses_self_credential,
          l.remote_name, l.modify_date
   FROM sys.linked_logins AS l
   JOIN sys.servers AS s ON l.server_id = s.server_id;

   A linked login with local_principal_id = 0 and a remote_name maps every local login to the same stored remote account, so anyone who can connect locally can act on the remote server as that account. Agent job steps of type CmdExec or PowerShell without a proxy run as the SQL Server Agent service account. See Database Links and Linked Servers.
8. Establish real usage before removing anything. Take a point-in-time view with sys.dm_exec_sessions (login_name, original_login_name, host_name, program_name, login_time, filtered on is_user_process = 1), then create a server audit specification with SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP and leave it running for at least one full business cycle, including month end and quarter end jobs. Treat host_name as a hint only: Microsoft notes the client supplies it and it can be wrong.

Turning results into findings

Remediation

Work the high findings first, one login at a time, with the owner on hand. Before any change, record the login's SID and its grants (script the login and its role memberships from SSMS or save steps 1, 3 and 4 output) so that you can restore it exactly.

• Disable instead of dropping: ALTER LOGIN [app_reporting] DISABLE; leaves grants, users and SID intact. Hold it disabled for at least one business cycle, then drop the database users, transfer ownership of any securables with ALTER AUTHORIZATION, and run DROP LOGIN.
• Remove server-level power: ALTER SERVER ROLE sysadmin DROP MEMBER [app_login]; and REVOKE CONTROL SERVER FROM [app_login];, then grant only database roles the application needs. For DBAs, Microsoft suggests granting CONTROL SERVER rather than sysadmin membership because it respects DENY.
• Enforce policy on SQL logins: ALTER LOGIN [app_login] WITH CHECK_POLICY = ON;. Setting CHECK_POLICY = ON initialises password history and enables lockout, so a misconfigured client retry loop can lock the login: test outside peak hours. CHECK_EXPIRATION = ON is rarely right for unattended accounts, because an expired password disables the login and stops the application; rotate on a schedule instead.
• Rotate a SQL login password: ALTER LOGIN [app_login] WITH PASSWORD = '...'; after the new secret is in the vault and the application can read it. Only sysadmin members can change the password of another sysadmin.
• Fix orphaned users: map to an existing login with ALTER USER [user] WITH LOGIN = [login]; or recreate the login with the original SID using CREATE LOGIN ... WITH PASSWORD = '...', SID = 0x...;. sp_change_users_login is deprecated.
• Change service accounts properly: always use SQL Server Configuration Manager, not the Windows Services console, which changes the account but not the related settings. Move standalone engines to a virtual account or gMSA (supported for standalone instances from SQL Server 2014 and for failover cluster instances and availability groups from 2016). Failover cluster instances cannot use virtual accounts and need a domain account or gMSA. Use separate accounts for the engine and Agent.
• Replace wildcard linked logins: drop the local_principal_id = 0 mapping with sp_droplinkedsrvlogin and add specific mappings for the logins that need the link.

Verify it worked

1. Re-run steps 2 to 5 and diff against the saved baseline: removed members and permissions must be gone, and nothing new should have appeared.
2. Confirm the application owner reports normal operation and check the audit file for FAILED_LOGIN_GROUP events from the changed login (error 18456 in the SQL Server error log is the same failure).
3. For a service account change, check sys.dm_server_services shows the new account and status_desc = 'Running', and that Kerberos still works for clients (no fall back to NTLM).
4. Check that SERVER_PRINCIPAL_CHANGE_GROUP, SERVER_ROLE_MEMBER_CHANGE_GROUP and SERVER_PERMISSION_CHANGE_GROUP recorded your changes: if your own change is not in the audit, an attacker's would not be either.

Rollback and common errors

Detection and measurement

Keep one server audit specification running permanently with SUCCESSFUL_LOGIN_GROUP, FAILED_LOGIN_GROUP, SERVER_PRINCIPAL_CHANGE_GROUP, SERVER_ROLE_MEMBER_CHANGE_GROUP, SERVER_PERMISSION_CHANGE_GROUP, LOGIN_CHANGE_PASSWORD_GROUP and SERVER_PRINCIPAL_IMPERSONATION_GROUP, and forward it to your SIEM. Alert on any addition to sysadmin, any enable of sa, interactive tools (SSMS, sqlcmd) connecting with an application login, and bursts of failed logins against SQL logins (ATT&CK T1110 Brute Force). The Database Audit Log Detection Guide covers rule design.

• Sysadmin count: members of sysadmin plus CONTROL SERVER grantees per instance, with a target the DBA team agrees.
• Policy coverage: percentage of enabled SQL logins with is_policy_checked = 1.
• Secret age: SQL logins whose password is older than your rotation standard.
• Managed service accounts: percentage of engine and Agent services on virtual accounts, MSA or gMSA.
• Owner coverage: percentage of non-human logins with a named owner in the register.
• Review currency: instances whose last completed audit is older than the review cycle.

Common failure patterns

• Auditing with a low-privilege login: without VIEW SERVER SECURITY DEFINITION, sys.server_principals returns only the caller and sa, and the report looks clean.
• Stopping at role membership: CONTROL SERVER and IMPERSONATE grants give the same power without appearing in sysadmin.
• Ignoring Windows groups: DOMAIN\SQL Admins in sysadmin looks like one login, but its AD membership may include service accounts and leavers.
• Dropping on a hunch: without a full-cycle login audit, the quarterly reporting login gets dropped and the SID with it.
• Changing service accounts in services.msc: Microsoft warns this does not update all the required settings.
• Leaving the secret where it was: rotating a SQL login password but leaving the old value in config files and scripts. See Database Credential and Access Security Guide.

Framework mapping

Practitioner checklist

• Run discovery from a dedicated audit login with VIEW SERVER SECURITY DEFINITION and VIEW SERVER SECURITY STATE.
• Export principals, SQL login policy flags and password age, server role members and server permissions for every instance.
• Record each service account and check its AD groups, SPNs and password age.
• Run sp_validatelogins and the orphaned user query in every database.
• Inventory credentials, Agent proxies and linked logins, and remove wildcard mappings.
• Run a login audit for a full business cycle before disabling anything.
• Disable, wait, then drop, keeping the SID and scripted grants.
• Move engine and Agent services to virtual accounts or gMSA through Configuration Manager.
• Keep the server audit specification running and alert on sysadmin changes.
• Repeat the audit on your access review cycle and diff against the last run.

Standards and references

• Microsoft Learn: sys.server_principals
• Microsoft Learn: sys.sql_logins
• Microsoft Learn: LOGINPROPERTY
• Microsoft Learn: Configure Windows service accounts and permissions
• Microsoft Learn: Password policy
• Microsoft Learn: SQL Server Audit action groups and actions
• Microsoft Learn: Troubleshoot orphaned users
• Microsoft Learn: SQL Server security best practices

Related NHI Mgmt Group resources: SQL Server Security Guide · Service Account Security Guide · Group Managed Service Accounts (gMSA) Guide · Database Privileged Access Review Template · NHI Dormancy Detection