1

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
2

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;
|improve this answer|||||
  • Thank you very much! – AiB Apr 10 '19 at 18:18
0

You dont have to reinvent the wheel. If you want a pure tsql based approach - use sp_ineachdb as suggested by @Dan.

I would suggest you use dbatools - Repair-DbaDbOrphanUser

e.g. Below command Finds and repairs all orphan users of all databases present on server 'sql2017'

Repair-DbaDbOrphanUser -SqlInstance sql2017

|improve this answer|||||
  • thank you for the suggestion. The problem is I can't create any persistent or temporally object into the database. I know it could be easily done using a temp table. But my project doesn't allow me doing that. – AiB Apr 10 '19 at 17:07
  • What about using powershell based - repair-dbadborphaneduser ? – Kin Shah Apr 10 '19 at 17:21
  • The requirement is using SQL only. Its is one of the rules I am developing for database hardening. – AiB Apr 10 '19 at 17:23
  • @AiB Your project allows you to call sp_msforeachdb but doesn't allow you to create a #temp table? And you can use any SQL you want but nothing vetted by the community and maintained in GitHub? – Aaron Bertrand Apr 10 '19 at 17:38
  • 1
    @AiB Sounds like a silly set of restrictions with no basis of justification in reality. Do they want you to do your job, or not? – Aaron Bertrand Apr 10 '19 at 17:49

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.