List Orphan User & role and delete Orphan User who Don't Have sysadmin Role

I am looking for a script that will list orphan user & roles and a script that will delete all orphan users who do not have sysadmin role. This needs to work with SQL Server 2000 & 2005.
Omega002Asked:
Who is Participating?
 
k_murli_krishnaConnect With a Mentor Commented:
All of these instructions should be done as a database admin, with the restored database selected.
This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Also, refer:
http://www.mssqltips.com/tip.asp?tip=1590
For orphaned role, refer:
http://www.eggheadcafe.com/forumarchives/SQLServerserver/Jun2005/post23408248.asp
 
0
 
k_murli_krishnaConnect With a Mentor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.