Problem is w/ .ADP..Re: SQL db Permissions for users not working

• Previous message: JK: "RE: sql server 2000/adp security problem"
• In reply to: JK: "Problem is w/ .ADP..Re: SQL db Permissions for users not working"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Mon, 12 Jul 2004 12:41:03 -0700

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, Hari, 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 Mike Gunderloy, Paul Schnitzler,
and Danny Lesandrini. Their time, interest and efforts are
greatly appreciated! :)

>-----Original Message-----
>Hi Hari,
>Thanks for your reply.
>All objects are owned by dbo (which is mapped to my user
name).
>
>Yes -- I logged on as one of the users with problems,
then connected to Query Analyzer, and I was able to access
the tables -- that I had specifically assigned to that
user, and the user defined role she belongs to, etc. I
was also able to verify persmissions 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 limits access to
users that are not dbo. Any ideas?
>
>Thanks for your time and help,
>Jocelyn
>
>"Hari Prasad" wrote:
>
>> Hi,
>>
>> Can you connect to SQl Server using Query analyzer
using the user with
>> problems and try doing a Query.
>> I feel that the object owner is not dbo, in this even
if you have previlages
>> you have to give qualified names like,
>>
>> select * from object_owner.table_name
>>
>> If the object owner is non dba use the
procedure "sp_changeobjectowner" (see
>> bokks online) to change
>> the owner to dbo.
>>
>> How to see the object owner
>>
>> 1. login as use with db_owner role
>> 2. use database name
>> 3. execute the below script:-
>>
>> select user_name(uid) as Object_owner,name,type from
sysobjects
>>
>> --
>> Thanks
>> Hari
>> MCDBA
>>
>>
>> "JK" <JK@discussions.microsoft.com> wrote in message
>> news:48388794-9A8B-4A5B-9BE0-
20D1324EBC50@microsoft.com...
>> > Hello,
>> >
>> > Please help -- the permissions that I had created for
other db users in
>> either SQL Enterprise manager (or via stored procedures
in query anaylyzer)
>> does not work for 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 other
fixes for this
>> problem.
>> > Thank you for your time - any help would be greatly
appreciated.
>> >
>> >
>>
>>
>>
>.
>

• Previous message: JK: "RE: sql server 2000/adp security problem"
• In reply to: JK: "Problem is w/ .ADP..Re: SQL db Permissions for users not working"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]