SQL Server2000 Orphaned user Solutions

server| Solution

SQL Server2000 Orphaned user Solutions
Keywords: Orphaned users
When you restore a database backup to another server, you may encounter problems with orphaned users. The following is a list of MS methods (from help) and their own methods
Method One:

Troubleshoot orphaned users
When you restore a database backup to another server, you may encounter problems with orphaned users. The following scenario shows and resolves the problem:

By executing sp_addlogin, rename the login janetl to dbo.
Sp_addlogin ' janetl ', ' dbo '
Back up the database. In this case, back up the Northwind.
BACKUP DATABASE northwindto DISK = ' C:\mssql\backup\northwnd '
Drop the database that you just backed up.
DROP DATABASE Northwind
Drop login.
Sp_droplogin ' Janetl '
Restores the backed up database.
RESTORE DATABASE northwindfrom DISK = ' C:\mssql\backup\northwnd '
Janetl logins cannot access the Northwind database unless guest logon is allowed. Although the janetl login has been deleted, it is still (as an orphaned row) displayed in the sysusers table:

Use Northwindselect *from sysuserswhere name = ' Janetl '
Resolving Orphaned user issues

Add a temporary login with sp_addlogin. Specifies the security identifier (SID) (from sysusers) for orphaned users.
sp_addlogin @loginame = ' Nancyd ', @sid = 0x32c864a70427d211b4dd00104b9e8a00
Use Sp_dropalias to drop temporary aliases that are part of the alias SID.
Sp_dropalias ' Nancyd '
Use Sp_dropuser to remove the original user (that is, orphaned users now).
Sp_dropuser ' Janetl '
Remove the original login with Sp_dropuser.
Sp_droplogin ' Nancyd '
Method Two:

Use master
DECLARE @SID varbinary (85)
--Get a new SID with the same name
SELECT @SID = SID
From sysxlogins
where [name]=] the login to be associated with an orphaned user

--open to allow updating of system table options (also can be done in Enterprise Manager's server Settings-> "Allow direct modifications to the system directory")

sp_configure ' allow updates ', 1
Reconfigure with OVERRIDE
Update orphaned user's database. dbo.sysusers
Set sid= @sid
Where name= ' Orphaned users '
sp_configure ' allow updates ', 0

Reconfigure with OVERRIDE