Fix orphan users

,

some times after restore database, you find your logins and users mapping is lossed and no user can now logon to database using application. I faced that many times. To map all your database users with master..syslogins. save this procedure in current database and execute it. All users will be mapped with logins.

CREATE PROCEDURE dbo.Usp_FixOrphanUsers 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

Rate

Share

Share

Rate