Can't Read Tables If Not In Sysadmin Server Role

Listed below are some basic info about my application.


.net 2.0 web application

SQL 2000 database

Single sign-on. Windows Authentication with impersonate set to true in web.config:

<authentication mode="Windows"/>

<identity impersonate="true"/>

Connection String in web.config: <add name="conn" connectionString="Data Source=MyRemoteSeverName;Initial Catalog=MyDBName;Integrated Security=SSPI; " providerName="System.Data.SqlClient;"/>




The problem I'm having now is that any users that are not in the sysadmin server role can't read any table. Say if I have 2 users:

Domain1User1 ( in db_datareader and db_datawriter Database Role, no sever role assigned)
Domain1User2 ( in db_datareader and db_datawriter Database Role, sysadmin Server Role)

Here is what's expected to happen if everything goes well.


A list of customers ( first name, last name, age, etc) in a gridview should show up after login.

After both users logged in, Domain1User1 received an exception message of "Object reference not set to an instance of an object".

Domain1User2, however, was able to see the list.

I checked SQL Server Logs, and found 2 items:
Login succeeded for user 'Domain1User1' ... Connection TRUSTED
Login succeeded for user 'Domain1User2' ... Connection TRUSTED.
Based on the log file, it appears that both users had good connections to the database. Then why is it that User1, which is not in sysadmin Server Role, was not allowed to make a query?

Now if I assign sysadmin Server Role to Domain1User1 as well, the User1 will be able to see the list without seeing the exception.

Can someone shed some light for me please ?