.NET Musings

As a follow up to my last post, Matt also gave me a stored proc (to load in Master) that can be executed in the database in question. So, create the sproc in Master, then switch to your database you just restored, create a new query window, and run "exec sp_fixusers". Yep, it's that simple!

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_fixusers] Script Date: 06/10/2008 18:50:21 ******/
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