Removing Orphan Users from All Databases on Server

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  November 18, 2002 at 12:00 am

  #105296

  Comments posted to this topic are about the item Removing Orphan Users from All Databases on Server

  Gregory A. Larsen, MVP

• Sunayana Shivashankar

  SSC Journeyman

  Points: 89

  More actions

  July 5, 2007 at 9:40 am

  #717440

  For SQL Server 2005,I also think you need to exclude the predefined database users,dbo and sys.The query needs to be modified as follows:

  select u.name from master..syslogins l right join sysusers u on l.sid = u.sid where l.sid is null and issqlrole 1 and isapprole 1 and (u.name 'INFORMATION_SCHEMA' and u.name 'guest'

  and u.name 'system_function_schema' and u.name 'dbo' and u.name 'sys')

• sroyenator

  SSC Rookie

  Points: 41

  More actions

  October 29, 2009 at 12:33 am

  #1072250

  Hi Greg,

  I run the Store Proc but when i go to individual databases and run EXEC sp_change_users_login 'Report';

  it still show me orphaned users..Please let me know if i am missing something