Why did the admin user for my RDS for SQL Server instance lose access and how can I gain it back?

Short description

When you create a new DB instance, the default admin user automatically receives certain permissions for the DB instance. After you create the DB instance, you can't change the admin username.

Note: It's a best practice not to use the admin user directly in your applications. Instead, use a database user that has the minimal required permissions for your application.

To restore the admin user's permissions, modify the DB instance and set a new admin user password.

Resolution

The admin user can't connect to the DB instance

The admin user might not be able to connect to the DB instance because an explicit DENY is set on the Connect SQL permission. By default, the Amazon RDS System Administrator (rdsa) login grants Connect SQL to the admin user. However, in Microsoft SQL Server, an explicit DENY takes priority over an explicit GRANT.

To resolve this issue, complete the following steps:

1. Use the login of the grantor who placed the explicit DENY on Connect SQL for the admin user login to connect to RDS for SQL server.
2. Run the following T-SQL command to revoke the explicit DENY. Replace master_user with your Amazon RDS admin user login and grantor_principal with your principal grantor:

   USE [master];GO
   REVOKE CONNECT SQL TO [master_user] AS [grantor_principal];
   GO
   

The admin user can't connect to a specific database

This can occur in the following circumstances:

• Another login account created the database. Also, the admin user login isn't mapped to a database user nor granted permissions to the database.
• The database user that was previously mapped to the admin user login and had valid permissions was explicitly deleted.

To resolve this issue, reset the admin user password to create a database user that's mapped to the admin user login for the deleted user. You also grant the db_owner fixed database role to the user. 

Note: The AWS Identify and Access Management (IAM) user that resets the password must have permission to perform the ModifyDBInstance action on the RDS resource.

When you update the admin user password, you restore the following for the admin user:

• System permissions
• Server-level roles
• Server-level permissions
• Access to system stored procedures
• Access to RDS-specific stored procedures

The admin user can't perform certain actions

The admin user has db_owner role permission on the database but can't perform certain actions, such as CONNECT, SELECT, INSERT, UPDATE, ALTER, and so on. This can occur when the database user that's mapped to the admin user login is explicitly denied certain permissions on the database.

To view the list of database roles and the database users that are members of the roles, run the following T-SQL command. Replace database_name with your database name:

USE [database_name];  GO
  SELECT DP1.name AS DatabaseRoleName,
   isnull (DP2.name, 'No members') AS DatabaseUserName
 FROM sys.database_role_members AS DRM
 RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
 LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Run the following commands to get the list of permissions that a user has in a specific database:

USE [database_name];GO
EXECUTE AS USER = 'master_user';
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

In the preceding example, the admin user is added to the db_denydatawriter and db_denydatareader fixed database roles. Even though the admin user is a member of the db_owner role, the explicit DENY prohibits SELECT, INSERT, UPDATE and DELETE permissions on the database.

To resolve this issue, complete the following steps:

1. Use the admin password to log in to the RDS for SQL Server instance.
2. Run the following T-SQL command to drop the admin user as a member of db_denydatawriter and db_denydatareader:

   USE [database_name];GO
   ALTER ROLE [db_denydatawriter] DROP MEMBER [master_user];
   ALTER ROLE [db_denydatareader] DROP MEMBER [master_user];
   GO
   

The admin user now has SELECT, INSERT, UPDATE, and DELETE permissions on the database.

Related information

Resetting the db_owner role password

Microsoft SQL Server security

DENY (Transact-SQL) on the Microsoft website