SQL Clone
SQLServerCentral is supported by Redgate
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 a_madhur@rediffmail.com


Total article views: 249 | Views in the last 30 days: 2
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


Orphaned Database Files

A neat way to detect if there are any orphaned SQL database files taking up space on your server.