Project and SQL role/user dbo problem

[Christophe Niel]

Hi all

I'm having the strangest thing with sql objects ownnership when i'm using
Access 2003.
A user is a member of a Domain Global Group in the AD.
This group is used in SQL enterprise manager to give the dbo role on a
database.
SQL Analyzer, Enterprise manager, no problem, my user has the dbo role, he
can access anything, delete everything, yay! great security!

So why, O why, can he not access the database as "DBO" when using a MS
ACCESS 2003 Project file ??
All the objects have " (dbo) " at the end on their name, everthing is read
only.
When I create object, the ownership is set to " mydomain\mylogin "

Except from the read-only problem, I wouldn't mind if I could use "
dbo.mytable " in the fonction I need, but the only one I'm really
interrested in is :
DoCmd.TransferSpreadsheet acImport, , "dbo.mytable" .....
and that doesn't work because it tries to create/find a table " dbo_mytable
" (where does this underscore come from?)

I can't use User authentication in SQL because there is way too many users,
so I use group authentication, maybe the problem is here.

Can anyone tell me why my user doesn't have the dbo role?

 

[Arvin Meyer]

Try removing the dbo qualifier in the Access UI after the tables are linked.
Also make sure that all forms have dbo as the table qualifier in the
property sheet.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Christophe Niel]

Ok, but I'm using a ADP . It's an Access Project, there is no linked tables
or property sheets, it's directly using the data on the SQL Server.

Thanks for this answer, but can you find another one

Best regards,
Christophe_Niel@fr_abnamro_com (replace '_' by '.' )

 

[Christophe Niel]

----- Original Message -----
From: "Christophe Niel" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Thursday, April 21, 2005 1:46 PM
Subject: Re: Project and SQL role/user dbo problem

Ok, but I'm using a ADP . It's an Access Project, there is no linked
tables or property sheets, it's directly using the data on the SQL Server.

Thanks for this answer, but can you find another one

(sorry for sniping)

Oh, instead of making bad jokes about search engines in some other post,
here is what I did find in the technet :
Q253180 : http://support.microsoft.com/default.aspx?scid=kb;en-us;253180
ACC2000: Table Created by User in Db_owner Role Makes User the Owner

Which looks similar to my problem, But I'm using Access 2003 with sp1...

 

[Christophe Niel]

Please, go to this forum : microsoft.public.access.adp.sqlserver to answer,
I'm sorry I didn't find this newsgroup before.

Best regards

 

[Arvin Meyer]

Ok, but I'm using a ADP . It's an Access Project, there is no linked tables
or property sheets, it's directly using the data on the SQL Server.

You are linked to SQL-Server tables with either ODBC or OLEDB. The only way
to directly use the data on a SQL-Server is with the Enterprise Manager or
Query Analyzer. If you don't think you are linked, try deleting the table
from the ADP. You will be able to do it (assuming permissions) but when you
go to the server itself, it will still be there. Then you'll see that all
you've deleted was the link.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Christophe Niel]

I know there is a link : you access the SQL objects (table, view, sp,
etc...) directly.

Now try and create a PROJECT in access and please, tell me where you can
find any information concerning this links!! That's the point of it being a
_project_ and not a _database_.

A "linked" table is something specific in access, when you do project this
is different, and FYI, the "Properties" option in the pop-up menu is grayed.

Anyway, thanks, but as you can see in another post, please anwser in
Microsoft.public.access.adp.sqlserver, it seems to me a more appropriate
newsgroup.

best regards
Christphe Niel