2005 Problems with Database Users after Restore to Different Server

  • Ok, using SQL 2000 for moving a server, I use to create the users on the new server and then just create a dummy database in preperation for the move of a database.  I would then just restore over the top of the dummy database with the production database and I was all set.  Since all the users were aleady there and were already assigned to the dummy database that is the same exact name as the production database, the restored database was perfectly in sync and the users's never new it was moved.

    Now, with SQL 2005, I do the same thing, or even just restore a new copy of the database over to a test or new server were the login's already exist and are already assigned to the database of the same name and it does not work.  The user's connect but can't get to the database, and if the database is there default, they can't connect becuase there is an error connecting to there default database.  I have to go in, remove the schema's and users from the database and then go and re-assign them to the database.

    Why is this happening, I would think that it should work the same as 2000 did, what has changed that is making me change the way I have done restore's.

  • Robert,

    Do you know the sp_change_users_login SP? It'll be your good friend. This way you can fix this problem even without creating the logins in advance. I'm just guessing, but your problem may originates from a security enhancement, and SQL2005 checks the SIDs as well. Since they're autogenerated, there's not too much chance to make them match. And - what a pity - you can't hack it via system tables, because you don't have the option to modify system tables. So try sp_change_users_login 'Auto_Fix', 'username' - maybe you can leave out username, I'm not sure. Check it in BOL .

  • Here is what I use to Migrate Databases from 2005 server to 2005 Server.   This script will not work on SQL2000.   This Code is from the MSDN SQLServer site, but it needed a couple of tweeks

    step 1 Run the following two scripts on the Master Database of the Leaving Server,  Yes Backup Master before you start and you must be logged on as a system Administrator.

    This will not tell you what the password are just what the hashed output is.

    Step 2 run EXEC sp_help_revlogin  in the SQL Management Studio (Query Analyser)

    It should produce output like the following

    -- Login: admin.sa

    CREATE LOGIN [admin.sa] WITH PASSWORD = 0x01003DA6E9296E4552028046BDC2CC130613BD570C080A5DE959 HASHED, SID = 0x23E00A11882CBC46BDE0C36DD5A121DE, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

    -- Login: MYDomain\Fred.Nerk

    CREATE LOGIN [MYDomain\Fred.Nerk] FROM WINDOWS

    -- Login: MyServer\SQLServer2005MSSQLUser$MyServer$MSSQLSERVER

    CREATE LOGIN [MyServer\SQLServer2005MSSQLUser$MyServer$MSSQLSERVER] FROM WINDOWS

    Step Three

    Cut and paste the resultant scripts -- like above -- on to the Target server.

    Step four

    restore your database from the leaving server to the target server.  The logins will alreay be there with the same SID as the Leaving server.

    All done

    Hope that helps.   remember backup up always and often

    CodeOn

    --SCRIPT START

    USE master

    GO

    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

      DROP PROCEDURE sp_hexadecimal

    GO

    CREATE PROCEDURE sp_hexadecimal

        @binvalue varbinary(256),

        @hexvalue varchar (514) OUTPUT

    AS

    DECLARE @charvalue varchar (514)

    DECLARE @i int

    DECLARE @length int

    DECLARE @hexstring char(16)

    SELECT @charvalue = '0x'

    SELECT @i = 1

    SELECT @length = DATALENGTH (@binvalue)

    SELECT @hexstring = '0123456789ABCDEF'

    WHILE (@i <= @length)

    BEGIN

      DECLARE @tempint int

      DECLARE @firstint int

      DECLARE @secondint int

      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

      SELECT @firstint = FLOOR(@tempint/16)

      SELECT @secondint = @tempint - (@firstint*16)

      SELECT @charvalue = @charvalue +

        SUBSTRING(@hexstring, @firstint+1, 1) +

        SUBSTRING(@hexstring, @secondint+1, 1)

      SELECT @i = @i + 1

    END

    SELECT @hexvalue = @charvalue

    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

      DROP PROCEDURE sp_help_revlogin

    GO

    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

    DECLARE @name sysname

    DECLARE @type varchar (1)

    DECLARE @hasaccess int

    DECLARE @denylogin int

    DECLARE @is_disabled int

    DECLARE @PWD_varbinary  varbinary (256)

    DECLARE @PWD_string  varchar (514)

    DECLARE @SID_varbinary varbinary (85)

    DECLARE @SID_string varchar (514)

    DECLARE @tmpstr  varchar (1024)

    DECLARE @is_policy_checked varchar (3)

    DECLARE @is_expiration_checked varchar (3)

    IF (@login_name IS NULL)

      DECLARE login_curs CURSOR FOR

          SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin

            FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

            WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

    ELSE

      DECLARE login_curs CURSOR FOR

          SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin

            FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

            WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin

    IF (@@fetch_status = -1)

    BEGIN

      PRINT 'No login(s) found.'

      CLOSE login_curs

      DEALLOCATE login_curs

      RETURN -1

    END

    SET @tmpstr = '/* sp_help_revlogin script '

    PRINT @tmpstr

    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

    PRINT @tmpstr

    PRINT ''

    WHILE (@@fetch_status <> -1)

    BEGIN

      IF (@@fetch_status <> -2)

      BEGIN

        PRINT ''

        SET @tmpstr = '-- Login: ' + @name

        PRINT @tmpstr

        IF (@type IN ( 'G', 'U'))

        BEGIN -- NT authenticated account/group

          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'

        END

        ELSE BEGIN -- SQL Server authentication

            -- obtain password and sid

            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

            EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

            -- obtain password policy state

            SELECT @is_policy_checked =

                CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

                FROM sys.sql_logins WHERE name = @name

            SELECT @is_expiration_checked =

                CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

                FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )

                + ' WITH PASSWORD = ' + @PWD_string

                + ' HASHED, SID = ' + @SID_string

            IF ( @is_policy_checked IS NOT NULL )

            BEGIN

              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

            END

            IF ( @is_expiration_checked IS NOT NULL )

            BEGIN

              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

            END

        END

         IF (@denylogin = 1)

        BEGIN -- login is denied access

          SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

        END

        ELSE IF (@hasaccess = 0)

        BEGIN -- login has exists but does not have access

          SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

        END

        IF (@is_disabled = 1)

        BEGIN -- login is disabled

          SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

        END

         PRINT @tmpstr

      END

      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin

      END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO 

    --SCRIPT STOP

  • As already mentioned sp_change_users_login is what you need to sync the db-user-sid's to the server user-sid.

    To script the SQL2000 users with their passwords use this on your sql2000 side:

    select 'exec sp_addlogin ['

    + name

    + '],'

    , password

    , ', @encryptopt=skip_encryption'

    from master..sysxlogins

    Run it to generate the addlogin statements, copy / paste the results from sql2000 to your sql2005 and run on your sql2005 bos

    To sync the users at SQL2005 database side (each database separately !)

    print 'print @@servername + '' / '' + db_name()'

    print 'go'

    go

    declare @username varchar(128)

    declare @Musername varchar(128)

    declare @IsNtName bit

    declare @sql_stmt varchar(500)

    declare @ExcludeWindowsAccounts Char(1)

    set @ExcludeWindowsAccounts = 'N' -- Y/N for windows accounts wil

    --cursor returns with names of each username to be tied to its respective

    DECLARE user_cursor CURSOR FOR

     SELECT su.name as Name, msu.name as MasterName , su.isntname

     FROM sysusers su

     left join  master.dbo.sysxlogins msu

       on upper(su.name) = upper(msu.name)

     WHERE su.sid > 0x00

     ORDER BY Name

    --for each user:

    OPEN user_cursor

    FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName

    WHILE @@FETCH_STATUS = 0

    BEGIN

     IF @username NOT IN ('dbo', 'list of names you want to avoid')  

     BEGIN

      if @Musername is null

        begin

          if @IsNtName = 1

       begin

      if @ExcludeWindowsAccounts = 'N'

      begin

      print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\'  + @username + ''')'

      print '   begin '

      print ' exec sp_grantlogin N''NtDomein**\'  + @username + ''''

      print ' exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+ db_name() + ''''

      print '   end'

      set @sql_stmt =  '--Windows account gehad'

      end

      else

      begin

       set @sql_stmt =  '--'

      end

     end

           else

     begin

           SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''''

     end

        end

      else

        begin

          SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''

        end

     

      PRINT @sql_stmt

     print 'go'

      print '--*** Warning: exec stmt commented !!! ***'

      --EXECUTE (@sql_stmt)

     END

     FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName

    END --of table-cursor loop

    --clean up

    CLOSE user_cursor

    DEALLOCATE user_cursor

    Print '** end User-synchronisation **'

    Copy /paste the results and run for user synchronisation.

    Need to be executed on every db of the server !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks for the replies everyone!  I used the sp_change_users_login with the following script, because it does require you to put the username you wish to fix with the auto_fix action.

    set

    nocount on

    create

    table #tmpuserfix (

    UserName

    varchar(50),

    UserSID

    varbinary(85))

    insert

    into #tmpuserfix execute sp_change_users_login report

    declare

    @usernametofix varchar(50)

    declare

    usercur cursor local static

    for

    select UserName from #tmpuserfix

    open

    usercur

    fetch

    next from usercur into @usernametofix

    while

    @@fetch_status = 0

    begin

    exec

    sp_change_users_login 'auto_fix', @usernametofix

    fetch

    next from usercur into @usernametofix

    end

    close

    usercur

    deallocate

    usercur

    drop

    table #tmpuserfix

  • The above did work like a charm because the report action shows the ones that need fixing. 

    Why does this reply page have such big spacing between lines.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply