Monday, February 22, 2010

How to find Orphaned users (Windows authenticated) in a restored database?

Very frequently, I need to restore databases from one server to another and sometimes it really gets difficult to find out the orphaned users in the restored database.  So, how do you find the orphan users in the restored database ?

Well for SQL Server logins, you can use the below stored procedure:

exec sp_change_users_login 'report' and this will list all the orphaned sql server logins.

But, how do you find the orphaned Windows authenticated users/groups? Below is a simple script to find out these orphaned users in the restored databases.

select users.name
from master..syslogins logins right join sysusers users on logins.sid = users.sid
where logins.sid is null and issqlrole <> 1 and isapprole <> 1  
and (users.name <> 'INFORMATION_SCHEMA' and users.name not in ('guest', 'dbo', 'sys'))


This can be used to write a script which will delete all the orphaned users or map it to the correct logins.

1 comment:

Abhinav Dhiman said...

This query still gives me the SQL logins. I am only looking for the Windows Login's . Can you please help?

Post a Comment