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