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 !!

5 comments:

  1. Parking Management Software
    online parking software
    Cloud based parking solutions
    Get Hold of the Top Violation and Guest Parking Management System in Town:

    With Web Parking Software, you will have a great chance to create property violations, vehicle violations and customize the fines. You can even opt to issue warnings instead of fines. Furthermore, occupants and registrants can review their violation online, pay online and appeal online. Occupants, registrants, users and tenants can pay fees online. Other charges and violations can be easily paid online by using your credit cards. The process is quite simple and easy and they will provide their valued clients with full accounting reports of all items paid and owed.
    Moreover, Web Parking Software has professional and well trained management team that has vast junderstanding, expertise and knowledge in providing their respected clients with excellent web based parking software that is worth seeking for. Once you decided to try and avail their services, you can be sure that you will never go wrong in choosing their software as the best violation and guest parking management system in town. The management team of Web Parking Software had undergone intensive training to assure their clients that they can meet and go beyond their expectations, demands, needs and desires. They worked hand in hand to ensure that they can perform their job perfectly and effectively.
    In terms of their service fee, clients don’t need to worry since the basic service of Web Parking Software is free. In the recent year, there are a huge number of companies that offer such services however choosing the best one like Web Parking Software is highly recommended. For those individuals who are tired of faxing their forms to their users, Web Parking Software is the ultimate solution to your problem. The violation and guest parking management system will allow you to manage, send, stores and upload all you important documents and forms. With the existence and help of Web Parking Software, you can list all vendors that include plumber, security and maintenance man.
    Today, Web Parking Software is now available for smart phone application. According to some of their customers, Web Parking Software is a great system that will allow them to have a full control of their residential and commercial properties even in the comfort of their office, home and iPhone. Visit their website at http://webparkingsoftware.com for more information about their web based parking software. They will be happy to assist and help you in the best way they can.



    ReplyDelete
  2. This script is lacking, I wouldn't use it.

    ReplyDelete
  3. Thank you so much

    ReplyDelete