Why Unmapped SQL Logins Are a Security Risk You Can't Ignore

SQL Server logins with no database mappings are accounts that exist at the server level but have no access granted to any database. They're orphaned credentials sitting in your security model, and they represent a real attack surface. Finding and reviewing them should be part of every SQL Server security audit.

In a well-managed environment, every login should exist for a reason. It should map to at least one database, or it should be a documented service account with a clear purpose. When logins accumulate over time without any database access, it's usually a sign that user accounts weren't cleaned up after staff left, application accounts were created and then superseded, or database migrations left behind stale credentials. None of those situations are good from a security standpoint.

What Does "No Database Mapping" Actually Mean?

When you create a SQL Server login, you're creating a server-level principal. That login can then be mapped to one or more databases as a database user. The mapping is what gives the login actual access to data. A login without any database mappings can still connect to the SQL Server instance, but it can't read or write data in any user database.

That might sound harmless. It's not. A login with no database mappings can still be used to probe the instance, attempt privilege escalation, or exploit vulnerabilities at the server level. If that login has a weak or default password, it becomes a low-effort entry point for an attacker. Microsoft's own security guidance recommends applying the principle of least privilege, which means no account should exist without a documented, current purpose.

There's also a compliance angle. Frameworks like the Australian Government's Essential Eight and ISO 27001 both require organisations to manage user access tightly and remove accounts that are no longer needed. Unmapped logins are exactly the kind of thing that shows up in an audit finding.

How to Find SQL Logins with No Database Mappings

The script below identifies all server-level logins that have no corresponding user mapped in any database. It works by collecting all database-level SIDs across every database on the instance, then comparing that list against the server-level principals. Any login whose SID doesn't appear in any database shows up in the results.

USE MASTER;

CREATE TABLE #dbusers (
    sid VARBINARY(85)
);

EXEC sp_MSforeachdb
    'INSERT #dbusers SELECT sid FROM [?].sys.database_principals WHERE type != ''R''';

SELECT name
FROM sys.server_principals
WHERE sid IN (
    SELECT sid
    FROM sys.server_principals
    WHERE TYPE != 'R'
    AND name NOT LIKE '##%##'
    EXCEPT
    SELECT DISTINCT sid
    FROM #dbusers
);

DROP TABLE #dbusers;

Run this against each instance you want to audit. The script needs to be executed in the context of the master database, which is why the USE MASTER statement is at the top.

Breaking Down What the Script Does

It helps to understand each component before you act on the results.

The temporary table stores SIDs collected from every database on the instance. A SID (Security Identifier) is the unique identifier that links a server login to its database users. This is the correct way to check mappings because it accounts for renamed logins and other edge cases that a simple name comparison would miss.

sp_MSforeachdb iterates through every database and collects the SIDs of all principals that aren't roles (type != 'R'). This is an undocumented stored procedure, but it's been a standard part of DBA toolkits for years. If you're running this in an environment where sp_MSforeachdb is disabled or unavailable, you'll need to replace it with a cursor or a dynamic SQL loop.

The final SELECT uses an EXCEPT clause to find server principals whose SIDs don't appear in any database. The filters exclude:

  • Built-in accounts (type != 'R' excludes server roles)
  • Certificate-mapped and other internal logins (the ##%## filter removes those system-generated accounts like ##MS_PolicyTsqlExecutionLogin##)

The result is a clean list of logins that have no presence in any user database.

What to Do with the Results

Getting the list is the starting point, not the finish line. Before disabling or dropping anything, work through these steps:

  1. Cross-reference with Active Directory. If you're using Windows Authentication logins, check whether the corresponding AD account is still active. A disabled AD account paired with an unmapped SQL login is a strong candidate for removal.

  2. Check SQL Agent jobs. Some service accounts are used exclusively by SQL Server Agent jobs and may not need database mappings in the traditional sense. Review your job configurations before removing anything.

  3. Review linked server configurations. Logins used for linked server connections sometimes don't have direct database mappings. Check sys.linked_logins before acting.

  4. Look at the login creation date and last login time. You can query sys.server_principals for the create_date and modify_date columns. For last login activity, check the sys.dm_exec_sessions DMV or your audit logs if you have C2 auditing or Extended Events configured.

  5. Disable before you drop. Never drop a login immediately. Disable it first, monitor for a period of two to four weeks, and only remove it once you're confident nothing is relying on it.

Common Findings and What They Mean

In practice, when you run this audit across a typical SQL Server environment that hasn't had regular security reviews, you'll often find a handful of categories:

  • Former employee accounts that were removed from Active Directory but never cleaned up in SQL Server
  • Application accounts from decommissioned systems that were migrated but the old logins were never removed
  • Test accounts created during development or troubleshooting and forgotten
  • Vendor accounts created for one-time support engagements that were never revoked

Each of these carries different risk. A vendor account with a known password that was never changed is a significant vulnerability. A former employee's Windows login that's already disabled in AD is lower risk but still represents clutter that should be removed.

How Often Should You Run This Audit?

For most environments, running a login audit quarterly is a reasonable baseline. High-security environments, or those subject to compliance requirements, should run it monthly. Ideally, this kind of check is automated and the results are reviewed as part of a regular security process rather than being a one-off exercise.

The script above is easy to schedule as a SQL Server Agent job with the results captured to a table for trend analysis. If you're seeing the same unmapped logins appear in every run, that's a process problem, not just a technical one. It means your offboarding and decommissioning procedures aren't including SQL Server in their scope.

Key Takeaways

  • SQL logins with no database mappings still exist at the server level and can be exploited, even without access to specific databases.
  • The script uses SID-based comparison across all databases, which is more reliable than name-based checks.
  • Always disable logins before dropping them, and monitor for at least two to four weeks before removal.
  • Common sources of unmapped logins include former staff accounts, decommissioned application credentials, and vendor access that was never revoked.
  • Regular login audits, run at least quarterly, should be part of every SQL Server security programme.

If you'd like a thorough review of your SQL Server security posture, DBA Services offers SQL Server health checks that cover login audits, permission reviews, and configuration assessments. We work with organisations across Australia to identify and close security gaps before they become incidents. Get in touch to find out what a health check would look like for your environment.