Removing Orphan Users from All Databases on Server

  • Greg Larsen

    SSC-Insane

    Points: 20610

    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

    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

    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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply