The list of databases in the connection dialog is not populated and connection fails if user is a member of db_denydatareader in any database

Description

Attempting to populate the Database drop-down list in the Database Connection dialog box returns an error:

"Unable to connect to database server to retrieve database list; please verify your server name, user name and password info and try again. Invalid database name."

Manually entering the database name returns an error:

"Failed to connect to the specified server. Do you want to continue? Invalid database name."

A connection file may be created but a connection cannot be made successfully.

Cause

If any of the following conditions are true the database list is not created and the connection fails:

• The connecting user has been added to the db_denydatareader role in any database, not just the one they are attempting to connect to.
• The connecting user is a member of a database role, including public, that has been added to the db_denydatareader role.
• The 'guest' user in any database has been added to the db_denydatareader role.

The built-in database role db_denydatareader exists in every database. Users who are added to this role cannot read any data in that database, regardless of any other explicitly granted or inherited permissions that may exist. Every member of a role added to db_denydatareader inherits this permission and is unable to read any data in the database.

The guest user is a built-in database user. Any permissions assigned to the guest user in a database are inherited by all the logins on the SQL Server instance, even if they are not users in that database.

Workaround

• To check to see who is a member of db_denydatareader; execute the following T-SQL statement in each database:
  
  

  EXEC sp_helprolemember db_denydatareader

  
  
• To remove the user from the role; execute the following T-SQL statement:
  
  SQL Server 2012:
  
  

  ALTER ROLE db_denydatareader DROP MEMBER user_name

  
  SQL Server 2008 and 2008R2:
  
  

  EXEC sp_droprolemember 'db_denydatareader', 'user_name'

  
  
  
• To remove a user's access to a database, simply revoke CONNECT permission.
  
  

  REVOKE CONNECT FOR user_name

  
  Users who do not have CONNECT permission to a database cannot see the database in the list of available databases to connect to in ArcGIS.
  
  

  Note:
  CONNECT permission for the guest account can be revoked in the same way. As a security precaution, explicit permission on any database object should never be granted to the guest account.