Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Find Orphan Logins in SQL Server2000

By madhur,


exec sp_change_users_login 'report' displays the list of orphan USERS in a database but we don't have any builtin SP which displays the orphan LOGINS.
By Orphan Logins (not users), I mean the Logins that don't have any access to any databases and are sitting idle on the server (also aren't member of any fixed server role).
We can look "AUSER" field of sp_helplogins but again this procedure displays some unwanted information also and moreover, this does not take into account if the login is a member of any fixed server role.

My script usp_OrphanLogins.sql takes care of all the abovementioned concerns.

Also, this SP gives a user friendly message and quits if any of the database is not accessible. This makes sense because we may have some databse restore in progress or some database offline at times.

I'm creating the SP in tempdb database and to exec it simply run EXEC tempdb..usp_OrphanLogins.

For any queries, mail me at


Total article views: 248 | Views in the last 30 days: 1
Related Articles

Fix orphaned users after restoring database to another server.

Script reassigns database orphan users to the matching SQL Server logins.


List orphaned users from all databases

The script returns all orphaned database users across a SQL Server.


SQL Server: List Explicit Permission on Database for Login and Database Role

As part of security audit of login's, we have discussed earlier about the orphan users , listing se...


Orphaned User

Orphaned User


Orphan/Inactive SQL 2005 Users

Monitor inactive and orphaned SQL logins


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones