After migration of MSSQL database, it is not possible to login and see database, error 4064 appears.

Created:

2016-11-16 13:02:54 UTC

Modified:

2017-07-25 11:13:09 UTC

1

Was this article helpful?


Have more questions?

Submit a request

After migration of MSSQL database, it is not possible to login and see database, error 4064 appears.

Applicable to:

  • Plesk 12.0 for Windows
  • Plesk 12.5 for Windows

Symptoms

After migration of MSSQL database from one server to another (not Plesk means), it is not possible to login and see database in the SQL Server Management .

There are no logins for database users to connect databases. When I try to connect to database using SQL Management Studio , the following error appears:

    Cannot connect to the localhost\\MSSQLSERVER2012
Additional information:
Cannot open user default database. Login failed
Login failed for user 'chahar'. (Microsoft SQL Server, Error: 4064)

Cause

After restoring a database from a remote server backup, the “Login name” is often disassociated. The database user is an "orphan", there is no login id associated with the user.

Resolution

Up to MSSQL Server 2012 there is system stored procedure which handles the mapping of new and existing logins to database users. Map the login to the database user in SQL Management Studio :

1) In sql query window type:

    USE <<the_name_of_db>>
GO
EXEC sp_change_users_login 'Report'
GO

This query will return all database users which are not connected to any login. Additionally you may use the following query to get list of all orphaned database users for all databases:

    USE master
SELECT 'USE '+ name+ ' EXEC sp_change_users_login ''Report'' '
FROM sys.databases
WHERE name not in ('master','msdb','model','distribution','tempdb')
ORDER BY name

2) In sql query window type:

    USE <<the_name_of_db>>
GO
EXEC sp_change_users_login 'Auto_Fix', '<<username>>'
GO

Example:

    use my_database
EXEC sp_change_users_login 'report';
EXEC sp_change_users_login 'Auto_Fix', 'test_user'

You can find more detailed information about stored procedure sp_change_users_login at https://msdn.microsoft.com/en-us/library/ms174378.aspx .

Have more questions? Submit a request
Please sign in to leave a comment.