Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-10
2
Medium Priority
?
878 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 1500 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 1500 total points
ID: 24117472
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

783 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