SQL Server 2000 users with sysadmin or db_datareader roles cannot see data that they do not own

Description

SQL Server 2000 users who are members of the sysadmin server role or database roles, such as db_owner or db_datareader, automatically see all the data in a geodatabase. Their permission is inherited by way of role membership.

SQL Server 2000 users with configurations outlined here inherit permissions from server or database roles and cannot see data unless they are explicitly granted SELECT permission or are the owners of the data.

This issue affects SQL Server 2000 users of ArcSDE 9.3.1 and SQL Server 2000 users of ArcSDE 9.2 Service Pack 6 with any of the patches outlined below.

The affected patches are as follows:
-ArcSDE 9.2 Connection Performance and Geodatabase Repair Patch
-ArcSDE 9.2 Service Pack 6 Memory Allocation Patch, and
-ArcSDE 9.2 Service Pack 6 Compress with Concurrent Editors Patch.

This does not affect users at ArcSDE 9.3 or 9.3 Service Pack 1.

Cause

This issue was inadvertently caused by a different connection performance enhancement that is part of ArcSDE 9.3.1 and the ArcSDE 9.2 Connection Performance and Geodatabase Repair Patch.

Installing ArcSDE 9.3.1 or installing any patch on ArcSDE 9.2 Service Pack 6 introduces this problem.

Workaround