Re: Access 2007, SQL 2000

Tech-Archive recommends: Fix windows errors by optimizing your registry

Since users are not dbo, and SQL 2000 used the user name as the schema identifier, you have to make sure that every reference to a SQL Server object is fully qualified with the dbo. schema identifer. That would apply to all sql statements in VBA code and Access properties like form and report record sources. It's also good practice to include the schema qualifer in stored procedures, views, check clauses, etc., but I don't think those are essential. Those objects run within the context of the object owner, which should be dbo.

Normally a user should not be able to create any new objects in the db, but if they do, those new objects will be in the userName schema, not the dbo schema, assuming the user is not a db owner. When Access links a table it puts the schema name in parentheses after the object name, since it doesn't support the period within the object name. In your case where the user is a db owner, but not a sysadmin, Access may mistakenly be linking the table with the username qualifier instead of dbo, while the object is correctly created by SQL Server in the dbo schema.

"Watson, Rick" <rawatson@xxxxxxxxxxx> wrote in message news:40E44F0F-C2E0-428E-B500-28FA3803BDA4@xxxxxxxxxxxxxxxx
We have an issue using ADPs from our Access 2007 clients connecting to our SQL 2000 server. This problem was solved in the old days by making people sysadmins, but I am determined to stop that practice.

I have a user who I have made a database owner of one of the databases. He connect to the database fine, and do things in it. If he creates a new view, and runs it, all is fine. However if he then tries to go back into "design view" for that view, he receives the message "You might not have permission to perform this operation, or the object Test_090109 might no longer exist in the database."

In looking at the datase, the owner of this is dbo, as it is the owner of everything else. He can go in and use design view on the other views.

So I had him try it with a table. He can open other tables in design view. But he created a new table and afterward tried to go back into design view. His message is subtly different "table 'Test_table_090209 (hisusername)' no longer exists in the database". So it is seeing his name, but when I look at the table in Management Studio, I see dbo.Test_table_090209. So the ADP is looking for his name there, but it isn't nor do I want it to be.

At least in the view, there isn't a reference to his username, but I'm thinking that is the same problem.

So there is something about the 2000 Schema and the adp that aren't figuring each other out. Does anyone have any ideas as to what? I of course can do all this, but I am in the sysadmin group.

So is there some way of convincing the ADP and SQL to default correctly?

.

Relevant Pages

  • Re: exporting into a SQL Server schema
    ... inside of the schema. ... The thing that's most confusing is that the dbo schema isn't ... take a look at the way the objects in the AdventureWorks database are ... It turns out that I'm moving some tables to a web hosted solution with sql ...
    (microsoft.public.access.externaldata)
  • Re: Stored Procedure Disappearing
    ... Did you use dbo. ... Qualifier to dbo or if you have used something else for the schema (owner) ... summary report which obtains data from a large stored procedure containing ...
    (microsoft.public.access.reports)
  • Re: rda push when owner is not dbo.
    ... If I launch the sql query analyzewr and try to run the ... I'm pretty configdent that the owner name not being qualified is the ... > I pull the table with rda to a local table named task. ... > I also created a test table in the same database with owner dbo and I ...
    (microsoft.public.sqlserver.ce)
  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... preferred it if SQL 2005 supported EXECUTE AS for views). ... I'm beginning to understand that OWNER lives somewhere between the ... the owner of the schema the object was created it. ... In many databases, dbo owns everything. ...
    (microsoft.public.sqlserver.security)
  • Re: object cant be accessed unless owner is specified - SQL 2000
    ... The only reasonable explanation is that at this point Sune was the ... the qualfied query with the owner ... thus his default schema was dbo. ... SQL 2000 certainly has schemas! ...
    (microsoft.public.sqlserver.security)