Another script that’s nice to have around is a fix orphaned login script. I use this in particular when restoring databases from one server to another, and you simply receive a login fails error.

Today, SSRS was trying to use an orphaned login and got:

· An error has occurred during report processing. (rsProcessingAborted)

· Query execution failed for dataset ‘Details’. (rsErrorExecutingCommand)

· For more information about this error navigate to the report server on the local server machine, or enable remote errors

 

USE [master]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_fixusers]

AS

BEGIN

       DECLARE @username varchar(25)

      

       DECLARE fixusers CURSOR

       FOR

       SELECT UserName = name FROM sysusers

       WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)

       AND SUSER_SNAME(SID) IS NULL

       ORDER BY name

      

       OPEN fixusers

       FETCH NEXT FROM fixusers INTO @username

       WHILE @@FETCH_STATUS = 0

       BEGIN

      EXEC sp_change_users_login

            ‘update_one’, @username, @username

      FETCH NEXT FROM fixusers INTO @username
   END 

       CLOSE fixusers

       DEALLOCATE fixusers

 

END

GO