locked
Login Failed for user ''. Reason Failed to open the explicitly specified database [Client: xxx.xx.x.xx] RRS feed

  • Question

  • I moved a dozen user databases from one 2005 instance on one machine to a 2008 R2 instance on another machine. The databases that I moved is displaying the message in a profiler trace "Cannot open databases "xxxx" request by the login. The login failed. Logon     Error: 18456, Severity : 14, State 38 Date\Time  Logon      Login failed for. Login failed for user "xxxxx\xxxxxxxxxx". Reason Failed to open the explicity specified database [Clientxxx.xx.x.xx]. Iused the backup and restore technique to move the user databases. Only these user databases that I moved is displaying the error. All other user databases seem to not be displaying these messages. On several of the user databases I deleted all the original database users with the exception of the users dbo, guest, INFORMATION_SCHEMA and sys. On one of the databases I even ran the sp_Changedbowner 'sa', true  stored procedure on changing the dbo user login to are sa account. Note the sa account has master as its default database. But that did not work either. I am still getting the messages on all the database that I moved to the 2008 R2 instance.
    Friday, August 15, 2014 7:52 PM

Answers

  • Hello,

    This behavior seems to be related to orphan logins. Please use the following resource to transfer SQL Server logins from the SQL Server 2005 instance to the SQL Server 2008 R2 instance.

    http://support.microsoft.com/kb/918992

    If you deleted the database users, once you transfer the logins or recreate the logins on the new instance, then go to the properties of each login and on the User Mapping assign permissions to databases for each login.

    Make sure the default database is correct for each login.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, August 15, 2014 8:30 PM
  • Have you tried fixing orphan users ? try fixing orphan logins using below code, it not working then go ahead with Alberto suggestion as mentioned in the other reply.

    --Part 1 - Find orphaned users in current Database
    sp_change_users_login @Action='Report';


    --Part 2 - Fix orphaned users in current Database
    DECLARE @SQL VARCHAR(100)

    DECLARE curSQL CURSOR
            FOR SELECT
                    'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name
                    + ''', ''' + name + ''''
                FROM
                    sysusers
                WHERE
                    issqluser = 1
                    AND name NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')

    OPEN curSQL

    FETCH curSQL INTO @SQL

    WHILE @@FETCH_STATUS = 0
          BEGIN
                EXEC (
                      @SQL
                    )
                FETCH curSQL INTO @SQL
          END

    CLOSE curSQL
    DEALLOCATE curSQL

    GO

    --Same as Part 1
    --You can comment this part out, but it shows you that the orphaned users were actually fixed.
    sp_change_users_login @Action='Report'


    Raju Rasagounder Sr MSSQL DBA

    Sunday, August 17, 2014 3:20 AM

All replies

  • Hello,

    This behavior seems to be related to orphan logins. Please use the following resource to transfer SQL Server logins from the SQL Server 2005 instance to the SQL Server 2008 R2 instance.

    http://support.microsoft.com/kb/918992

    If you deleted the database users, once you transfer the logins or recreate the logins on the new instance, then go to the properties of each login and on the User Mapping assign permissions to databases for each login.

    Make sure the default database is correct for each login.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, August 15, 2014 8:30 PM
  • Have you tried fixing orphan users ? try fixing orphan logins using below code, it not working then go ahead with Alberto suggestion as mentioned in the other reply.

    --Part 1 - Find orphaned users in current Database
    sp_change_users_login @Action='Report';


    --Part 2 - Fix orphaned users in current Database
    DECLARE @SQL VARCHAR(100)

    DECLARE curSQL CURSOR
            FOR SELECT
                    'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name
                    + ''', ''' + name + ''''
                FROM
                    sysusers
                WHERE
                    issqluser = 1
                    AND name NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')

    OPEN curSQL

    FETCH curSQL INTO @SQL

    WHILE @@FETCH_STATUS = 0
          BEGIN
                EXEC (
                      @SQL
                    )
                FETCH curSQL INTO @SQL
          END

    CLOSE curSQL
    DEALLOCATE curSQL

    GO

    --Same as Part 1
    --You can comment this part out, but it shows you that the orphaned users were actually fixed.
    sp_change_users_login @Action='Report'


    Raju Rasagounder Sr MSSQL DBA

    Sunday, August 17, 2014 3:20 AM
  • I was also getting same error ,

    Added user which was failing  Login failed for. Login failed for user "xxxxx\xxxxxxxxxx"  on remote computer  local administrator group issue resolved.

    Sunday, October 7, 2018 12:01 AM