SQL Server 2000/adp problem

[JK]

Hello,

Please help -- the permissions that I had created for db users (in SQL Enterprise manager and also via stored procedures in query anaylyzer) does not work for non other users using my .adp applcation.

Here are the details:

As DBO (database owner), I have no problems accessing all dbo.objects in our SQL Server 2000 in my database (using windows nt integrated security) within my .adp (access data project file, office 2003 version). But when I grant select, insert, update on certain objects in SQL Enterprise Manager (v 8.0) and/or executing stored procedures in query analyzer for other users, the permissions (for other users) that I created do not work in the .adp file. I tried different strategies, such as, giving permissions via db roles, then adding users to those roles; I also tried adding logins that are win user groups, making them database users then giving permissions, but all of these variations did not work…

I have installed sp3a, and I haven’t found any hot fixes for this problem.
Thank you for your time – any help would be greatly appreciated.

 

[Jk]

I have some good news. my problem got resolved today!
So I just want to drop this note to share the resolution
and wanted to say thank you, everyone, for your help and
time

--------------------------------------
Part of the cause and resolution is addressed in MS
knowledge base article 313253:

"Cause: You do not have SELECT permissions on the
SysObjects system table in the database. Access relies on
the SysObjects table when it performs various tasks, such
as opening reports or determining which stored procedures
to display in the Database window.



Resolution:

"Grant the user (or the Public database role) SELECT
permission on the SysObjects system table in the
database. "



After granting the public role Select permission on the
SysObjects system table, the user is then able to see the
objects, as well as open the tables and run stored
procedures from the adp's database window. (Btw, select
permissions on syscolumns, systypes and syscomments system
tables for other users or public role are also required by
Access adp, but this was not mentioned in article 313253.)

However, the list boxes in my forms, which uses
parameterized stored procedures still did not work as
intended.

With help from Microsoft Tech Support (team members from
SQL Server 2000, Webdata, and Access 2003 contributed), we
figured out that qualifying the database owner (dbo in my
case) or adding dbo to the object name (ie.,
dbo.procedurename) in the list box's row source resolves
the problem.

Sincerely,
Jocelyn

ps. I'm also grateful to Hari Prasad, Kevin McDonnell,
Mike Gunderloy, Paul Schnitzler, and Danny Lesandrini.
Their time, interest and efforts are greatly
appreciated!

-----Original Message-----
Hello,

Please help -- the permissions that I had created for db

users (in SQL Enterprise manager and also via stored
procedures in query anaylyzer) does not work for non other
users using my .adp applcation.

Here are the details:

As DBO (database owner), I have no problems accessing all

dbo.objects in our SQL Server 2000 in my database (using
windows nt integrated security) within my .adp (access
data project file, office 2003 version). But when I grant
select, insert, update on certain objects in SQL
Enterprise Manager (v 8.0) and/or executing stored
procedures in query analyzer for other users, the
permissions (for other users) that I created do not work
in the .adp file. I tried different strategies, such as,
giving permissions via db roles, then adding users to
those roles; I also tried adding logins that are win user
groups, making them database users then giving
permissions, but all of these variations did not workâ?¦