Why did the master 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 master user automatically receives certain privileges for that DB instance. You can't change the master user name after the DB instance is created.

Note: It's a best practice not to use the master user directly in your applications. Instead, use a database user created with the minimal privileges required for your application.

If you accidentally delete the master user's permissions, you can restore them by modifying the DB instance and setting a new master user password. For more information, see Master user account privileges.

Resolution

The following are common scenarios that might lead to the master user losing access connecting to the DB instance. Or the master user might not be able to connect to and access a specific user database.

Scenario 1: The master user can't connect to the DB instance due to an explicit DENY

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

To fix this, do the following:

1.    Connect to RDS for SQL Server using the login of the grantor who placed the explicit DENY on Connect SQL for the master user login.

2.    Use the following T-SQL command to revoke the explicit DENY. In the following example, the RDS master user login is master_user and the grantor principal is grantor_principal. Change these values to match your use case.

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

Scenario 2: The master user can't connect to a specific database because it isn't mapped to a user in the database

This might occur in the following circumstances:

• The database was created by another login account. And, the master user login isn't mapped to a database user in the database and granted privileges to the database.
• The database user previously mapped to the master user login with proper permissions was explicitly deleted.

To resolve this issue, reset the master user password. Resetting the password creates a database user mapped to the master user login if that user was deleted. It also grants the db_owner fixed database role to the user. For instructions on resetting the master user password, see How do I reset the master user password for my Amazon RDS DB instance?

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

Updating the master user password does the following:

• Grants the master user the db_owner database-level role to a database created by another user.
• Restores system privileges to the master user.
• Restores server-level roles to the master user.
• Restores server-level permissions to the master user.
• Restores access to system stored procedures to the master user.
• Restores access to RDS-specific stored procedures to the master user.

Scenario 3: The master user can't perform certain actions

The master user has db_owner role permission on the database but perform certain actions, such as CONNECT, SELECT, INSERT, UPDATE, ALTER, and so on. This might occur when the database user mapped to the master user login was explicitly denied certain permissions on the database.

To see the list of database roles, and which database users are members of those roles, run the following T-SQL command. In the following replace database_namewith the correct values for your use case.

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 see the list of permissions a user has in a particular database. In the following example, replace database_name with the correct value for your use case.

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

In this example, the master user is added to the db_denydatawriter and db_denydatareader fixed database roles. Despite being a member of the db_owner fixed database role, the deny privileges of db_denydatawriter and db_denydatareader prohibit SELECT, INSERT, UPDATE and DELETE permissions on the database.

To resolve this issue:

1.    Log in to the RDS for SQL Server instance using the master user.

2.    Use the following T-SQL command to drop the master user as a member of these two roles:

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

After the commands complete, the master user has SELECT, INSERT, UPDATE, and DELETE permissions on the database restored.

For more information about the specific roles the master user has, see Master user account privileges.

---

Related information

Resetting the db_owner role password

Microsoft SQL Server security

DENY (Transact-SQL)