Hello,

Please help -- I created permissions for db users in SQL Enterprise manager 
(and also tried using stored procedures in query anaylyzer). However, the pe
rmissions do not work for other users using my access project (.adp).

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) withi
n my .adp (access data project file, office 2003 version). But when  I grant
ed select, insert, and update on certain objects in SQL Enterprise Manager (
v 8.0). However, the permissions (for other users)  that I created do not wo
rk in the .adp file. (I had also tried executing stored procedures in query 
analyzer to grant permission for other users). I tried different ways, such 
as, creating roles, giving permissions via those roles, then adding users to
 those roles; I also tried adding logins that are win user groups, making th
em 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.

[ Post a follow-up to this message ]

Validate that a user connecting with Query Analyser, using Windows
Authentication can execute a stored procedure.
If this doesn't work the ADP project won't either.

Thanks,

Kevin McDonnell
Microsoft Corporation

This posting is provided AS IS with no warranties, and confers no rights.

[ Post a follow-up to this message ]

Hi Kevin,
Thanks for your reply. I'm sorry for posting this problem twice -- as you ma
y have noticed already...  But I'm grateful if you can continue to share any
 ideas that you may have... as this problem has really caused me a set-back 
to our roll-out....

In Hari Prasad's reply, he's advised me to connect to Query Analyzer as one 
of the users as you suggested. Here is the result of that test...

Yes -- I logged on as one of the users with problems, then connected to Quer
y Analyzer, and I was able to access the tables -- that I had specifically a
ssigned to that user, and the user defined role she belongs to, etc.  I was 
also able to verify persmis
sions granted to public.

At least now, we're able to narrow down the permission problem to my ADP app
 (2002-2003 file format). I poked around, but I can't see anything that limi
ts access to users that are not dbo. Any ideas?

Thanks for your time and help,
Jocelyn

ps. As the test user, I was able to execute all procedures as well in query 
analyzer but not in ADP (I used the stored procedures I found in Albrecht am
d Nicol's book, "MS Access Projects with MS SQL Server." Unfortunately, the 
problem that I'm experienci
ng is not mentioned there.)


"Kevin McDonnell [MSFT]" wrote:

> Validate that a user connecting with Query Analyser, using Windows
> Authentication can execute a stored procedure.
> If this doesn't work the ADP project won't either.
>
> Thanks,
>
> Kevin McDonnell
> Microsoft Corporation
>
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
>
>

[ Post a follow-up to this message ]

Yes.  Try using SQL Profiler to capture the traffic when you're using the
ADP project.
One of the columns in the profiler data is the NT username.  This will
allow you  to verify that the user is connected using the correct
credentials as well.

It could be that the context of the initial connection is being made as the
developer and not the user you intended.

Thanks,

Kevin McDonnell
Microsoft Corporation

This posting is provided AS IS with no warranties, and confers no rights.

[ Post a follow-up to this message ]

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, Kevin and 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
SysObejcts 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 MS' 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 J


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


>-----Original Message-----
>Yes.  Try using SQL Profiler to capture the traffic when
you're using the
>ADP project.
>One of the columns in the profiler data is the NT
username.  This will
>allow you  to verify that the user is connected using the
correct
>credentials as well.
>
>It could be that the context of the initial connection is
being made as the
>developer and not the user you intended.
>
>Thanks,
>
>Kevin McDonnell
>Microsoft Corporation
>
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>
>
>.
>

[ Post a follow-up to this message ]

Hi,

probably this is not a security problem, but a problem with ms-accesss. I ha
d the same probem and found out, that ms-access .adp projects need a primary
 key in any table. If there is a table with no primary key defined, you cann
ot make an insert or update, even if you have the correct user-rights assign
ed.

Good Luck,

Rainer


quote:
Originally posted by jkadach 
Hello,

Please help -- I created permissions for db users in SQL Enterprise manager 
(and also tried using stored procedures in query anaylyzer). However, the pe
rmissions do not work for other users using my access project (.adp).

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) withi
n my .adp (access data project file, office 2003 version). But when  I grant
ed select, insert, and update on certain objects in SQL Enterprise Manager (
v 8.0). However, the permissions (for other users)  that I created do not wo
rk in the .adp file. (I had also tried executing stored procedures in query 
analyzer to grant permission for other users). I tried different ways, such 
as, creating roles, giving permissions via those roles, then adding users to
 those roles; I also tried adding logins that are win user groups, making th
em 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. 

[ Post a follow-up to this message ]