BUG-000095963: Users created with the Create Database User tool in SQL Server are able to see unregistered tables and views even though they do not have SELECT permission

Description

When connecting to a geodatabase in SQL Server, certain users are able to see all unregistered tables and views, despite not having been granted SELECT permission to them. However if one of these objects is selected, an error message is returned and the user is unable to see any data or properties of these tables or views. This behavior is present in ArcGIS for Desktop 10.2.1 with Utilities & Telcom Update Patch 5 (UTUP5), 10.4, 10.4.1, and ArcGIS Pro 1.2.

Cause

Users created with the Create Database User geoprocessing tool are granted VIEW DEFINITION permission so that they will be able to see a list of other users and roles in User/Role dialog box (accessed from the Privileges dialog box). VIEW DEFINITION permission allows users to see SQL Server database metadata including things like lists of users in that database, and lists of database objects like tables and views.

A fix was made in ArcGIS 10.4 for BUG-0000917818 (to improve performance of connections to large geodatabases for non-DBO users). To improve connection performance, ArcGIS was changed to rely on the database system catalog to filter tables, which is considerably faster than checking permissions on each table in the database. However, users with VIEW DEFINITION permission see the views and tables in the database system catalog tables, even if they do not have access to their contents.

Workaround

This behavior will be addressed in the next release of ArcGIS. If you are using ArcGIS Pro 1.2, ArcGIS for Desktop 10.4, 10.4.1, or 10.2.1 with UTUP5 and you are experiencing this behavior, you can revoke VIEW DEFINITION permission from the affected user. Be aware that revoking this permission means the user will no longer see a list of available users or roles in the ArcGIS Desktop User/Role dialog box. Object privileges can be managed in this dialog box by manually typing the name of the user or role, but all the available user or role names will not appear in the dialog box.

1. Using SQL Server Management Studio, navigate to the database where the user’s permissions must be modified.
2. Right-click the database icon and choose Properties.
3. Select the Permissions page.
4. Select the User.
5. Under Permissions for <user>, select the Explicit tab, scroll down to View definition and uncheck the Grant check box.
6. Click OK.