- Rebuilding and Recovering the <tt>master</tt> Database
- Potential Problems in Rebuilding and Restoring the <tt>master</tt> Database
- Recovering Other Databases after <tt>master </tt> Has Been Rebuilt
- Notes on Logins and Users after a <tt>master</tt> Rebuild
- Program to Generate Logins from the User Database
- Remapping Orphaned Users
- Recovering msdb
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
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.