When moving databases across servers, you face the same problem as when upgrading servers: users are server specific, but permissions are databases specific. They are not bound by UserName, but through a SID (security identifier).
When adding the same UserName entries to a new server that already has the databases restored, you get error messages like these:
When adding the same UserName entries, then restoring the databases, it won’t work, because the matching SIDs don’t exist.
There are many sites explaining the “Error 21002” and pointing to sp_change_users_login.
But sp_change_users_login (and the ALTER USER UserName WITH LOGINI = UserName2 in SQL Server 2005 and higher) is not the actual fix to the problem: it will re-add the user with a new SID, then correct the SID in the database currently in use.
Actually you are after “Orphaned” users: users that are defined in the various databases, but not present in the user list on the SQL Server instance. Running sp_change_users_login with the “Report’ parameter on the “new” server will show a list of orphaned users.
An even better way to show Orphaned Users is by running the ShowOrphanUsers script (thanks Vyas, wish more people republish your stuff with attribution!). In my own version (sourcecode is below), I have added an extra UserSID column of type varbinary(85).
In order to transfer users to a new server, you need a sp_help_revlogin stored procedure. Depending on the version of your SQL Server (7/2000/2005/2008), you need a slightly different version of a script that creates sp_help_revlogin for you. All versions are available at NetNerds.net.
–jeroen
via: Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008.
USE master;
GO
IF OBJECT_ID ('dbo.ShowOrphanUsers') IS NOT NULL
DROP PROCEDURE dbo.ShowOrphanUsers
GO
CREATE PROC dbo.ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS,
[UserSID] varbinary(85)
)
SET NOCOUNT ON
DECLARE @DBName sysname, @Qry nvarchar(4000)
SET @Qry = ''
SET @DBName = ''
WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)
IF @DBName IS NULL BREAK
SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
SID AS [UserSID]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
)'
INSERT INTO #Results EXEC (@Qry)
END
SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
END
GO
