Error Reported When a New Account Is Used to Log In to an RDS for SQL Server Instance

Scenario

When a new account (for example, newlogin) is used to log in to an RDS for SQL Server instance after being created using rdsuser, an error message is displayed, indicating that the account does not have the permission to log in to the msdb database.

Figure 1 Error message

Possible Causes

To prevent unknown accounts from easily obtaining permissions on the msdb database, RDS for SQL Server has been hardened and the default guest login permission on the msdb database is revoked. As a result, new accounts cannot log in to this database by default.

Figure 2 Checking login permissions

Solution

Grant the public permission on the msdb database to the new account as user rdsuser. The procedure is as follows:

Method 1:

1. Use SQL Server Management Studio (SSMS) to log in to the instance as user rdsuser.
2. Right-click the new account (for example, newlogin) and view its properties.

   Click User Mapping, select msdb, ensure that the public role is selected, and click OK.

   Figure 3 Granting the public permission
   
3. Use the new account (newlogin) to log in to the instance again. No error is reported.

Method 2:

1. Use Data Admin Service (DAS) to log in to the instance as user rdsuser.
2. Run the following SQL statements to grant permissions on msdb to the new account (for example, newlogin):

   USE [msdb]
    GO
    CREATE USER [newlogin] FOR LOGIN [newlogin]
    GO

3. Use the new account (newlogin) to log in to the instance again. No error is reported.