Saturday, October 31, 2009

SQL Server: Map Orphaned Users

I've put together the following stored procedure that will map ''orphaned" users in a database.  This often happens when you restore a database into a new environment.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_map_orphaned_db_users]    Script Date: 10/31/2009 19:44:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
 * PROCEDURE NAME:  sp_map_orphaned_db_users
 * DESCRIPTION:
 * Maps orphaned database users to server logins of the same name.
 *
 */
ALTER PROCEDURE [dbo].[sp_map_orphaned_db_users]
(
 @with_report BIT = NULL
,@database NVARCHAR(128) = NULL
,@report_only BIT = NULL /* Pass NULL or 0(zero) to execute and 1 to show actions that would be taken. */
)
AS
BEGIN

    SET NOCOUNT ON

    SELECT
        @report_only = ISNULL(@report_only, 0)
       ,@database = ISNULL(RTRIM(LTRIM(@database)), '')
       ,@with_report = ISNULL(@with_report, 0)

    DECLARE
        @UserName SYSNAME
       ,@dbname SYSNAME
       ,@sql NVARCHAR(4000)
       ,@orphanTableName NVARCHAR(128)

    SELECT
        @orphanTableName = '##orphans_' + REPLACE(CAST(NEWID() AS NVARCHAR(128)), '-', '')

    CREATE TABLE #orphans
    (
     UserName SYSNAME
    ,UserSID VARBINARY(85)
    ,dbname SYSNAME
    )

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END
        CREATE TABLE ' + @orphanTableName + '
        (
            UserName SYSNAME,
            UserSID VARBINARY(85),
            dbname SYSNAME
        )'
    EXEC (@sql)
   
    IF @database = ''
        BEGIN

            SELECT
                @sql = N'
            USE [?]
            CREATE TABLE #report
            (
                UserName SYSNAME,
                UserSID VARBINARY(85)
            )

            INSERT INTO #report
            EXEC sp_change_users_login @Action=''Report''

            ALTER TABLE #report
            ADD dbname SYSNAME NULL

            UPDATE #report
            SET dbname = ''?''

            INSERT INTO ' + @orphanTableName + '
            SELECT
                UserName,
                UserSID,
                dbname
            FROM #report'

            EXEC sp_msforeachdb @sql
        END
    ELSE
        BEGIN

            SELECT
                @sql = '
        USE [' + @database + ']
        CREATE TABLE #report
        (
            UserName SYSNAME,
            UserSID VARBINARY(85)
        )

        INSERT INTO #report
        EXEC sp_change_users_login @Action=''Report''

        ALTER TABLE #report
        ADD dbname SYSNAME NULL

        UPDATE #report
        SET dbname = ''' + @database + '''

        INSERT INTO ' + @orphanTableName + '
        SELECT
            UserName,
            UserSID,
            dbname
        FROM #report'

            EXEC (@sql)

        END

    SELECT
        @sql = N'
        INSERT INTO #orphans
        SELECT
            UserName,
            UserSID,
            dbname
        FROM ' + @orphanTableName

    EXEC (@sql)

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END'
    EXEC (@sql)

    DECLARE @userCount INT

    SELECT
        @userCount = COUNT(*)
    FROM
        #orphans o
        INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    DECLARE rc CURSOR LOCAL FAST_FORWARD
        FOR SELECT
                o.UserName
               ,o.dbname
            FROM
                #orphans o
                INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    OPEN rc

    FETCH NEXT FROM rc INTO @UserName, @dbname

    WHILE @@FETCH_STATUS = 0
        BEGIN

            SELECT
                @sql = 'USE ' + @dbname + ' EXEC sp_change_users_login @Action=''update_one'', @UserNamePattern=''' + @UserName + ''', @LoginName=''' + @UserName + ''''
            IF @report_only = 1
                BEGIN
                    PRINT 'WOULD HAVE ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END
            ELSE
                BEGIN
                    EXEC (@sql)
                END

            IF @with_report = 1
                AND @report_only = 0
                BEGIN
                    PRINT 'ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END


            FETCH NEXT FROM rc INTO @UserName, @dbname

        END

    IF (@userCount > 0)
        BEGIN
            PRINT CAST(@userCount AS NVARCHAR(50)) + N' USERS PROCESSED.'
        END
    ELSE
        BEGIN
            PRINT 'NO ORPHANED USERS TO PROCESS.'
        END


    CLOSE rc
    DEALLOCATE rc
END