Thursday, July 31, 2014

Fix orphan for all the database on a single shot.

Please change the RESULTS TO TEXT GRID and run the below query. Results given by this query need to run in Query

       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%''
       '

      
       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%''
       '