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




No comments:

Post a Comment

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...