How do I restore the primary user's access for my Amazon RDS for SQL Server instance?

Short description

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

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

Note: Don't use the primary user for application database connections. Instead, create a login with only the permissions required for your application's specific tasks.

Resolution

Note: In this article, primary user refers to the master user for your Amazon RDS for SQL Server instance.

The primary user might lose permission to connect to the DB instance, access a specific database, or perform certain actions. Complete the steps in the following section that matches your situation.

The primary user can't connect to the DB instance

The primary user can't connect to the DB instance when the Connect SQL permission is set to an explicit DENY. By default, the Amazon RDS System Administrator (rdsa) login grants Connect SQL to the primary user. However, in Microsoft SQL Server, an explicit DENY takes priority over an explicit GRANT.

To resolve this issue, complete the following steps:

1. Connect to the RDS for SQL Server instance with the login credentials of the user who set the explicit DENY on the Connect SQL permission for the primary user.
2. Run the following T-SQL command to revoke the explicit DENY:

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

   Note: Replace master_user with your RDS primary user login and grantor_principal with your principal grantor.

The primary user can't access a specific database

The following are reasons why the primary user can't access a specific database:

• Another login account created the database. The primary user login has no database user mapping and no database permissions.
• An administrator deleted the database user that mapped to the primary user login and had valid permissions.

To resolve the issue, reset the primary user password. This creates a database user that maps to the primary user login. Alternatively, create a database user for the primary user login in the affected database and grant the user db_owner permission.

When you reset only the primary user password, this operation doesn't cause downtime. However, if you simultaneously make other modifications to the DB instance, such as a change to the instance class, then those combined changes might cause downtime. The AWS Identify and Access Management (IAM) user who resets the password must have permission to perform the ModifyDBInstance action on the resource.

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

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

The primary user can't perform certain actions

The primary user has db_owner role permission on the database but can't perform certain actions, such as CONNECT, SELECT, INSERT, UPDATE, and ALTER. This occurs when an administrator explicitly sets DENY permissions on the database that override the db_owner role permissions.

Run the following T-SQL command to view the list of database roles and the database users who are members of the roles:

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;

Note: Replace DATABASE_NAME with your database 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

Note: Replace master_user with primary user and DATABASE_NAME with your database name.

In the preceding example, an administrator adds the primary user to the db_denydatawriter and db_denydatareader fixed database roles. Even though the primary user is a member of the db_owner role, the explicit DENY overrides SELECT, INSERT, UPDATE and DELETE permissions on the database.

To resolve this issue, complete the following steps:

1. Use the primary password to log in to the RDS for SQL Server instance.
2. Run the following T-SQL command to remove the primary 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

Note: Replace DATABASE_NAME with your database name and MASTER_USER with primary user.

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

Related information

Resetting the db_owner role membership for master user for Amazon RDS for SQL Server

Microsoft SQL Server security

DENY (Transact-SQL) on the Microsoft website