sql server ophaned user 문제 해결

Master DB에 저장된 로그인 정보와 각 데이터베이스에 저장된 로그인 정보의 SID가 불일치 할 경우 OrphanUsers 문제가 발생 합니다.
이경우 보통 다음의 프로시저를 사용합니다.

sp_change_user_login
1. 현재 사용자와 로그인 관의 매핑에 대한 보고서 표시
EXEC sp_change_users_login 'Report';
2. 데이터베이스 사용자를 새 SQL Server 로그인에 매핑
--Create the new login.
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database user MB-Sales to login MaryB.
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
3. 사용자를 로그인에 자동으로 매핑하고 필요한 경우 새 로그인 만들기
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO

sp_validatielogins
Windows 로그인을 사용하는 경우 사용합니다. MSDN 도움말을 참고하세요.

sp_changedbowner
orphaned dbo user 문제를 해결 하는 경우 사용합니다. MSDN 도움말을 참고 하세요.

관련 링크
Q168001 PRB: User Logon and/or Permission Errors After Restoring Dump
Q274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
Q246133 INF: Transfer Logins and Passwords Between SQL 7.0 or SQL 2000 Servers
Q240872 How to Resolve Permission Issues When a Database Is Moved Between SQL Servers

- OrphanUser를 보여주는 저장 프로시저
출처 : http://vyaskn.tripod.com/troubleshooting_orphan_users.htm

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
    )

    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]
                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