Tuesday, 26 March 2013

Finding and dropping Orphan Users in a DB

Below script can be used to find and drop the orphan users in a Database

 
 
Below script will list all the Orphan Users in a DB, we can select the output of [Script_To_Drop_User] column of the table variable and execute the same to Drop the user.
 
 -- Change the DB as per your DB Name
 

Use [PubDB]
declare @tbl_orpusers table (name varchar(260),[sid] varchar(100))

insert @tbl_orpusers
exec sp_change_users_login 'report'

select name [Orphan User],'DROP USER ['+name+']' [Script_To_Drop_User]from @tbl_orpusers



Checking the Orphan Users:



 
 
 
 
 
 
 
Below Link can be used to work on Orphan users in a DB,
 
 

 
 

No comments:

Post a Comment