Access ADP, filter lookup & SQL Server 2000

[xvblack]

Hi - I have an Access ADP front ending a SQL Server 2000 database. This has
been working fine for some time but I now have a problem that I have spent
many days trying to fix in vain.

The problem revolves around using the filter lookup property on some
textboxes on the Access forms. When I use the form everything works fine as I
am an administrator (we use NT Authentication on SQL Server) but when a
normal user tries to use it they just get the 'NULL/IS NOT NULL' default
values.

I have read many posts and have made sure that the record source qualifier is
set to 'dbo' and have used SQL Profiler to trace what is happening. I believe
the problem lies somewhere with the permissions that normal users have (or
don't have!) but I can't see what.

The Profiler trace showed that a temporary SQL table is created by a system
stored procedure (sp_MShelpcolumns) and my account inserts records into it
whereas a user account doesn't.

The Access form uses a stored procedure as it's underlying record source -
this actually runs ok whether as myself or as a normal user. What the user
actually wants to do is to do a filter by form query against this recordset.

When this happens Access must fire off some sort of query against the
database to populate any controls that have the filter lookup property set.
This is what isn't happening for the user - but when I do it then it works
fine! The only differences are the SQL Server permissions that I have as
opposed to a normal user ('admin' vs 'user').


Anyone got any ideas??

 

[AkAlan via AccessMonster.com]

Have you tried stepping through the event to see exactly where the error
occurs?

 

[xvblack via AccessMonster.com]

What exactly do you mean by 'stepping through'? I've followed the events
through the trace that is output by SQL Profiler. That is how I know a User
account does not insert records into the temporary table whereas my account
does.

Originally all our users were mistakenly set up with Active Directory
accounts that belonged to a group that had administrator rights and they
could do the filter by form no problem. Once we discovered our error and
removed the Users from the administrative group the problem with the filter
by form rose it's ugly head.

If you use XP_LOGINFO on SQL Server it shows the Users privileges as 'user'
whereas it was 'admin' before they were removed from the erroneous Active
Directory group.

Have you tried stepping through the event to see exactly where the error
occurs?

Hi - I have an Access ADP front ending a SQL Server 2000 database. This has
been working fine for some time but I now have a problem that I have spent

[quoted text clipped - 26 lines]

Anyone got any ideas??

 

[AkAlan via AccessMonster.com]

I meant steping through the VBA code to see where it takes you and where it
faults. That is assuming there is an event to trigger on. My guess is you
are using a stored procedure,function or view as a filter for the affected
text box and the users do not have the correct rights to it. Look at all
your text and combo boxes and make sure all users have rights to them.

What exactly do you mean by 'stepping through'? I've followed the events
through the trace that is output by SQL Profiler. That is how I know a User
account does not insert records into the temporary table whereas my account
does.

Originally all our users were mistakenly set up with Active Directory
accounts that belonged to a group that had administrator rights and they
could do the filter by form no problem. Once we discovered our error and
removed the Users from the administrative group the problem with the filter
by form rose it's ugly head.

If you use XP_LOGINFO on SQL Server it shows the Users privileges as 'user'
whereas it was 'admin' before they were removed from the erroneous Active
Directory group.

Have you tried stepping through the event to see exactly where the error
occurs?

[quoted text clipped - 4 lines]