So I restored a SQL 2000 database into SQL 2005. The database comes with its own users as usual, but there are no server logins for the users.

I created the logins and when I use the SSMS GUI to create the User Mapping to the existing database users, it says the user already exists.

What would I do to map the new server logins to the existing database users? I'm trying to avoid recreating the database users so I don't have to setup their permissions again. GUI or TSQL will be fine.

TIA

you can try EXEC sp_change_users_login 'Auto_Fix', 'userloginname'Jaime E. Maccou

Thanks! That works for all but one. In the original database, one of the logins was mapped to the database user dbo. When I try using @Action='Update_One' it says dbo is a forbidden value.

I used sp_changedbowner to the login I want mapped to dbo. Is this the recommended way to accomplish this?

Assign ownership to another dbo account, drop and recreate account owner and reassign same permissions.


Jaime E. Maccou

Also to Identify orphan user on your database you can run this script. Please notice table name which stores information related to logins.

CREATE PROC exec 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


Jaime E. Maccou

For more information on troubleshooting orphaned users, here's a link that may be able to help you:

http://vyaskn.tripod.com/troubleshoo...phan_users.htm

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp