NIM085951 for ArcSDE/Enterprise Geodatabase

Bug ID Number	NIM085951
Submitted	October 24, 2012
Last Modified	April 2, 2025
Applies to	ArcSDE/Enterprise Geodatabase
Version found	10.1
Version Fixed	10.2
Status	Fixed

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

Remove the user or role from the db_denydatareader role in all databases.

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.