Home > Articles > Data > SQL Server

  • Print
  • + Share This
  • 💬 Discuss

Remapping Orphaned Users

Quite often after you restore a database, there are user login, security, and permission problems because SQL Server requires a database login and you also have to be a user of the database. When you restore a database, you are quite often just restoring users to the database and not restoring server logins. Thus, you end up in a situation that I call orphaned users: a database with users but a server with different login SIDs for those users.

SQL Server provides a stored procedure (sp_change_users_login) to synchronize users and logins, but it can be tedious to use if there are more than a few users. The following is a script that automates much of this and helps on large databases with many users. It matches up users to SQL Server logins by name and then links the SIDs together so there are no orphaned users. What's important to remember is that the user and login have to pre-exist. If they don't, read the section "Notes on Logins and Users after A master Rebuild," which goes over creating logins when only users exist.

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

NOTE

It is important to go over the final results to ensure that the security for each user is correct. The tool will attempt to match users and logins properly, but there is a chance that a user could be mismatched and get the wrong level of security. Always check your work.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus