2

There are several logins in my SQL Server that don't have user mappings to any database; they are simply dummy logins.

Someone created the login but it does not have any associated users.

I need to a script to list out those logins.

Thanks in advance.

2

The query below is a good starting point. It returns a list of logins that have no matching user in any attached database. Be aware there are many reasons a login might exist at the server level, but not have an explicitly created user in any user database. Before removing the logins shown by this code, you should validate that they are not needed. As an example, this code may list the service accounts used to run SQL Server services; removing them might be a bad idea.

It filters out server roles, since they cannot be added to databases as users. Similarly for certificate-mapped logins, since they can only be used for code signing.

DECLARE @cmd nvarchar(max);
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'SELECT dp.sid
FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
WHERE dp.sid IS NOT NULL
'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE';

PRINT @cmd;

DROP TABLE IF EXISTS #database_sids;
CREATE TABLE #database_sids
(
    [sid] int NOT NULL
);
INSERT INTO #database_sids ([sid])
EXEC sys.sp_executesql @cmd;

SELECT sp.name
    , sp.type_desc
FROM sys.server_principals sp
WHERE NOT EXISTS (
    SELECT 1
    FROM #database_sids ds
    WHERE ds.sid = sp.sid
    )
    AND sp.[type_desc] NOT IN (
          N'SERVER_ROLE'
        , N'CERTIFICATE_MAPPED_LOGIN'
        )
ORDER BY sp.name;

The query gathers the list of security identifiers from each database in the SQL Server instance, then returns logins that don't appear in that list of security identifiers.

There are a couple of "gotchas" with this approach. If any databases are offline when the code runs, users in the offline database(s) will not be excluded from the output, resulting in possible false-positives.

Since the script uses the security identifier, instead of the name of the user, it may return false positives for users and logins with the same name, but different SIDs. This can easily occur If you restore a database from a different source SQL Server and run this code against it without "fixing" the orphaned users first1.


1 - I am associated with the site SQLServerScience.com and wrote this blog post

| improve this answer | |
-1

Following query first collects all users from databases (including system databases), then validate with all available logins in sever. Result shows list of user created logins that are not mapped any database in server:

Beware that the sp_MSforeachdb is undocumented procedure, you would probably want use custom stored procedure that can make script to run across the databases:

Declare @Temp Table (sid varbinary(85), name nvarchar (100) );

insert into @Temp (sid, name)

exec sp_MSforeachdb
'Use [?];
select sid, name
from sys.database_principals as dp
where type in (''S'', ''U'', ''G'') and sid is not null and (dp.name not in (''dbo'', ''guest'') and dp.name not like ''##%'' ) '


select   
        sp.name,
        sp.sid, 
        sp.type_desc,
        'drop Login [' + sp.name + ']' as FixCommand 
from sys.server_principals as sp
    left outer join @Temp as dp on sp.sid = dp.sid
Where sp.type in ('S', 'U', 'G') and ( not sp.name = 'sa' and sp.name not like '##%' and sp.name not like 'NT %')and dp.name is null 
GO

Following query would help you to list-out Orphaned Users from all user databases along with fix command (last column)

exec sp_MSforeachdb
'Use [?];
select  DB_NAME () as DBName, dp.name, 
        dp.principal_id, 
        dp.type, 
        dp.type_desc, 
        dp.sid, 
        sp.sid,
        ''use '' + cast(DB_NAME () as varchar(50)) + ''; if not exists (select containment from sys.databases where not containment = 0) drop user ['' + dp.name + '']'' as FixCommand
from sys.database_principals as dp
        left outer join sys.server_principals as sp on dp.sid = sp.sid
where  dp.principal_id > 4 AND sp.sid is null and dp.type in (''S'', ''U'', ''G'')'
go

For the particular database scope, usually the Orphaned Users may occurred in user databases while restoring from a backup of different server, therefore system databases are not in our scope (as per following script):

select  DB_NAME () as DBName, dp.name, 
        dp.principal_id, 
        dp.type, 
        dp.type_desc, 
        dp.sid, 
        sp.sid,
        'drop user [' + dp.name + ']' as FixCommand
from sys.database_principals as dp
        left outer join sys.server_principals as sp on dp.sid = sp.sid
where  dp.principal_id > 4 AND sp.sid is null and dp.type in ('S', 'U', 'G')
| improve this answer | |
  • 3
    Would recommend against using sp_msforeachdb - it's undocumented and can miss databases – George.Palacios Jul 29 '19 at 9:53
  • 1
    Also this is almost the opposite of what the OP wanted - this is for users with no mapped logins (Orphaned users), OP wants logins with no mapped users if I'm correct. – George.Palacios Jul 29 '19 at 9:56
  • Yeah I understand that - but it doesn't answer the OP question so you probably won't be marked as the answer. – George.Palacios Jul 29 '19 at 10:27
  • @GeorgePalacios Thanks for pointing that. i miss that, will be added as edited post. However, hope that the Orphaned User queries would clarify the logic, all we need to switch the tables location to get list of logins instead of users – Shekar Kola Jul 29 '19 at 10:38
  • Don't forget that because users are within the scope of a database, you'd actually have to left join to ALL database.sys.database_principal names, so if there is more that one database this solution won't work. – George.Palacios Jul 29 '19 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.