Removing Orphan Users from All databases on Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Larsen » Removing Orphan Users from All databases on...

Removing Orphan Users from All databases on Server

Rate Topic

Display Mode

Topic Options

Author

Message

Greg Larsen

Posted Sunday, November 17, 2002 12:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/glarsen/orphan_user.asp

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com

Post #8232

Sunayana Shivashankar

Posted Thursday, July 05, 2007 9:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 13, 2009 11:39 AM
Points: 1, Visits: 5

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')

Post #379288

sroyenator

Posted Thursday, October 29, 2009 12:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, February 26, 2012 4:48 PM
Points: 1, Visits: 89

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

Post #810525

« Prev Topic | Next Topic »

Permissions