SQL Server – Using Cursor and Foreachdb Loop

sql servert-sql

I want to create a cursor (without creating a temp table) to get all orphan users in my SQL Server instances. Here is what I've come up with:

Declare @Orphan_users table (username sysname)
DECLARE @orphan_users_cursor CURSOR  
set @orphan_users_cursor = cursor 
FOR
select * from (
insert into @Orphan_users 
set EXEC SP_MSFOREACHDB' USE [?]
SELECT dp.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 authentication_type_desc = ''INSTANCE'''--) 

OPEN @orphan_users_cursor
FETCH NEXT FROM @orphan_users_cursor INTO  @Orphan_users

Best Answer

I think your table should also include the database name, otherwise once you identify a user that is abandoned in some database on your system, how are you going to find them?

Anyway here is a way to do it by building a single query rather than executing a query database by database (and relying on a system procedure that is undocumented, unsupported, and has community replacements because it's so buggy). It isn't better or faster, just easier to understand and troubleshoot, IMHO.

DECLARE @AbandonedUsers TABLE(db sysname, username sysname);

DECLARE @sql  nvarchar(max) = N'SELECT db = NULL, username = NULL WHERE 1 = 0',
        @base nvarchar(max) = N'
UNION ALL SELECT PARSENAME(N''$qn$'',1), dp.name COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM $qn$.sys.database_principals AS dp
  WHERE dp.authentication_type = 1
    AND NOT EXISTS (SELECT 1 FROM sys.server_principals AS sp
      WHERE sp.SID = dp.SID)';

SELECT @sql += REPLACE(@base,N'$qn$',QUOTENAME(name))
  FROM sys.databases
  WHERE state = 0; -- might also add AND database_id > 4 to avoid common system DBs

INSERT @AbandonedUsers(db, username) EXEC sys.sp_executesql @sql;

SELECT db, username FROM @AbandonedUsers;
Related Question