Listing Server Logins Not Mapped to Any Database User

Logins not mapped to any database user are review candidates, not a delete list. A login with no database user can still have real work to do at the server level.

Why the list tempts you

Every server collects logins over the years. A project ends, someone leaves, a test account stays behind. Then a security review asks for a cleanup, and the quick idea is: find every login that no database uses, and drop it.

That idea is half right. Finding the list is easy. Acting on it blindly is how you get a call at 2 AM from an application that cannot connect. So let me build the list first, and then show you why each row needs a second look.

The demo creates two logins and one database on your server, and removes all three at the end. You need permission to create logins, so use a test instance. Never use a production server for a demo like this.

Create one mapped and one unmapped login

DemoMappedLogin gets a database user. The user is named ReportUser, on purpose, so the names do not match. DemoUnmappedLogin gets no user at all, but I give it a server role so it has a reason to exist.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
IF SUSER_ID(N'DemoMappedLogin') IS NOT NULL DROP LOGIN DemoMappedLogin;
IF SUSER_ID(N'DemoUnmappedLogin') IS NOT NULL DROP LOGIN DemoUnmappedLogin;
GO
CREATE LOGIN DemoMappedLogin
    WITH PASSWORD = N'Demo#Mapped-2026-xQ7', CHECK_POLICY = OFF;
CREATE LOGIN DemoUnmappedLogin
    WITH PASSWORD = N'Demo#Unmapped-2026-xQ7', CHECK_POLICY = OFF;
ALTER SERVER ROLE dbcreator ADD MEMBER DemoUnmappedLogin;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
CREATE USER ReportUser FOR LOGIN DemoMappedLogin;

Compare by SID, not by name

A login and its database user are tied together by the SID, a binary identifier, and not by their names. That is why ReportUser still counts as DemoMappedLogin’s user. A query that matched on names would wrongly call that login unmapped.

The script below walks through every online database you can access. It collects the SIDs of the SQL users, Windows users and Windows groups. It also writes down any database it could not read, because a database you skipped is a hole in the report.

USE master;
DROP TABLE IF EXISTS #MappedSids;
DROP TABLE IF EXISTS #SkippedDatabases;

CREATE TABLE #MappedSids (DatabaseName sysname, UserName sysname, sid varbinary(85));
CREATE TABLE #SkippedDatabases (DatabaseName sysname, Reason nvarchar(4000));

INSERT #SkippedDatabases (DatabaseName, Reason)
SELECT name, N'Offline or no access'
FROM sys.databases
WHERE state <> 0 OR ISNULL(HAS_DBACCESS(name), 0) = 0;

DECLARE @db sysname, @sql nvarchar(max);
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE state = 0 AND HAS_DBACCESS(name) = 1 ORDER BY name;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @sql = N'USE ' + QUOTENAME(@db) + N'; INSERT #MappedSids
            SELECT DB_NAME(), name, sid FROM sys.database_principals
            WHERE type IN (''S'', ''U'', ''G'') AND sid IS NOT NULL;';
        EXEC sys.sp_executesql @sql;
    END TRY
    BEGIN CATCH
        INSERT #SkippedDatabases VALUES (@db, ERROR_MESSAGE());
    END CATCH;
    FETCH NEXT FROM db_cursor INTO @db;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;

Read the report

Now compare the server logins to the collected SIDs. Anything with no match is a candidate. The query skips the internal logins whose names start with ##.

SELECT p.name, p.type_desc, p.is_disabled
FROM sys.server_principals AS p
WHERE p.type IN ('S', 'U', 'G')
  AND p.name NOT LIKE N'##%'
  AND NOT EXISTS (SELECT 1 FROM #MappedSids AS m WHERE m.sid = p.sid)
ORDER BY p.name;

SELECT DatabaseName, Reason FROM #SkippedDatabases ORDER BY DatabaseName;

DemoUnmappedLogin is in the list. DemoMappedLogin is not, even though its user has a different name. Your list will also show service accounts and other logins that exist for good reasons. The second result is the databases the scan skipped. On a quiet test server it is usually empty.

Look at what an unmapped login can still do

Here is the trap. The list says “no database uses this login”. It says nothing about server roles, jobs, linked servers or services that sign in with it. So check the server role membership before you decide anything.

SELECT m.name AS MemberName, r.name AS RoleName
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
ORDER BY m.name, r.name;

DemoUnmappedLogin appears here as a member of dbcreator. It has no database user, yet it can create databases. Dropping it because “nothing uses it” would remove a real permission. Check agent jobs, linked servers, application connection strings and Windows group access as well.

One more limit. This catalog scan says nothing about recent activity. A login can be unmapped and busy, or mapped and unused for years. Before you disable anything, ask the owner, disable first, and wait. Disabling is easy to undo. Dropping is not.

The last block removes the database and both demo logins. Its final query looks for any leftover Demo login and returns no rows.

USE master;
DROP TABLE IF EXISTS #MappedSids;
DROP TABLE IF EXISTS #SkippedDatabases;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
IF SUSER_ID(N'DemoMappedLogin') IS NOT NULL DROP LOGIN DemoMappedLogin;
IF SUSER_ID(N'DemoUnmappedLogin') IS NOT NULL DROP LOGIN DemoUnmappedLogin;

SELECT name FROM sys.server_principals WHERE name LIKE N'Demo%Login';

Treat the report as the start of a conversation, not the end of one.

An unmapped login is not an unused login, it is a login that needs a second look.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.