Changing the Permission to View All Databases

Scenarios

You can use a stored procedure to grant the permission to view all databases for a specified account. If this permission is revoked, only the master and tempdb databases can be viewed.

Precautions

• The stored procedure can only be executed by the rdsuser user or the database login account. The login account has all the permissions of the rdsuser user on RDS for SQL Server instances. For details about the stored procedure for creating a database login account, see Creating a Database Account.
• By default, all users are assigned the public role and can view all databases in the instance. However, they cannot access or edit the databases that they do not have permissions for.
• The database viewing permissions of rdsuser and other built-in accounts cannot be changed. For details about the built-in accounts, see Database Account Security.

Prerequisites

An RDS for SQL Server DB instance has been connected. For details about how to connect to an instance through the SQL Server client, see Connecting to a DB Instance Through a Public Network.

Procedure

Run the following command to configure the permission to view all databases (excluding the master and tempdb databases) for a user:

EXEC master.dbo.rds_view_any_database @user, @action ;

• @user: Name of the user.
• @action: Operation to be performed.
  • deny: Do not allow the user to view all databases.
  • revoke: Allow the user to view all databases.

Example

• Do not allow the testuser user to view all databases:

  EXEC master.dbo.rds_view_any_database 'testuser','deny' ;

• Allow the testuser user to view all databases:

  EXEC master.dbo.rds_view_any_database 'testuser','revoke' ;