Error when logging on to an ApsaraDB RDS for SQL Server instance: cannot open user default database.Login failed

Problem description

An error occurs when you log on to an ApsaraDB RDS for SQL Server instance. The error message is as follows:

cannot open user default database.Login failed.

Cause

The default database for the logon user does not have an associated user.

SELECT 
 sp.name AS LoginName,
 sl.default_database_name AS DefaultDatabase
FROM 
 sys.server_principals sp
JOIN 
 sys.sql_logins sl ON sp.principal_id = sl.principal_id where sp.name='jmuser03'

In the following example, the default database for the user jmuser03 is jmdb02. If the jmdb02 database does not have an associated user, the logon attempt fails and this error is reported.

You can use a SQL statement to check whether an associated user exists for this logon user.

USE jmdb02; -- Replace [jmdb02] with the actual database name.
GO
SELECT 
    sp.name AS LoginName,
    dp.name AS UserName
FROM 
    sys.server_principals sp
JOIN 
    sys.database_principals dp ON dp.sid = sp.sid
WHERE 
    sp.type IN ('S', 'U', 'G')  -- S=SQL Login, U=Windows User, G=Windows Group
AND 
    sp.name = 'jmuser03'; -- Replace [jmuser03] with the actual database name.

Solution

Create an associated user for the logon user in the default database.

USE jmdb02; -- Replace [jmdb02] with the actual database name.
GO
CREATE USER jmuser03 -- Replace [jmuser03] with the username that you want to create.
FOR LOGIN jmuser03; -- Replace [jmuser03] with the actual logon username.
GO

References

For more information about how to log on to an ApsaraDB RDS for SQL Server database, see Connect to an ApsaraDB RDS for SQL Server instance.

Applies to

ApsaraDB RDS for SQL Server