Solved

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

Posted on 2009-04-10
2
861 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:Omega002
  • 2
2 Comments
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 500 total points
ID: 24117418
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
 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 500 total points
ID: 24117472
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use odbc in vb to connect to ms sql 14 40
SQL Pivot add row totals 2 16
t-sql split string into multiple rows 7 42
Sql Query 4 19
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now