How To Fix Orphan User For All Databases in SQL Server



Couple of days back during migration we have to fix orphan user for 50+ databases and as you know i am very lazy doing manual work so got below script over web to fix same in couple of mins. Thanks to peapole who are writing on SQL server communities.

Script 1: Find Orphan users

/**********************************************************/
  EXECUTE master.sys.sp_MSforeachdb ' USE [?]; Select ''?'' ;
                EXEC ?.dbo.sp_change_users_login   ''report'' '
/**********************************************************/


Script 2 : Generate Script To Fix Orphan users

/**********************************************************/ 
 EXECUTE master.sys.sp_MSforeachdb ' USE [?];
select ''EXEC ?.dbo.sp_change_users_login '''''' +  ''update_one'' +
 '''''''' +  '',['' + '''' +  name + ''''+ ''],['' +'''' +   name + '']'' + + ''''
from sysusers 
where sid NOT IN (select sid from master..syslogins ) 
AND islogin = 1 AND name NOT LIKE ''%guest%''  '
/**********************************************************/

So All users are fixed finally !!

No comments:

Post a Comment