Access 2007, SQL 2000

[Watson, Rick]

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?

Thanks,

Rick

 

[Paul Shapiro]

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.

 

[Sylvain Lafontaine]

A common and old problem with ADP: in many place, it will add the username
as the schema/owner but at other times, it will not. If you take a look
with the SQL-Server Profiler, you'll probably see that ADP is qualifying the
name of the view or of the table with the name of the user - calling it for
example "UserName.View1" instead of "View1" or "dbo.View1". When the name
is fully qualified with the schema, SQL-Server cannot substitute "dbo.View1"
for "UserName.View1"; something it could do for "View1".

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Mary Chipman [MSFT]]

The issue here is directly related to your trying to curb the use of
sysadmin. When a user is a member of sysadmin (or uses the 'sa' login,
which is mapped to the sysadmin server role), the owner of the object
is 'dbo', which is not the same as db_owner (the database owner).
Naming in SQL Server is not the same as Access, i.e., dbo.MyTable is
the full name of the object, not MyTable. So if user Fred creates a
table, and Fred is not a member of sysadmin, then the table's name is
Fred.MyTable, which will co-exist happily in the same database
alongside dbo.MyTable. Another issue is that permissions will need to
be assigned separately to Fred.MyTable if other users besides Fred
need to work with that table. SQL Server will automatically look for
objects that are not fully-qualified in the dbo schema/namespace,
which is why everything worked smoothly before (sysadmins always have
full permissions to everything on the server). These issues are
ameliorated with user-schema separation in SQL Server 2005/2006 --
users can be assigned a default schema and permissions assigned to a
schema in advance so they can create objects in a given schema without
having to be granted the elevated permissions of sysadmin or db_owner.
But in the meantime you're stuck with SQLS 2000, so what I'd recommend
is the following:

1. transfer ownership of all objects to dbo (you have to be logged on
as a sysadmin to do this). You are definitely on the right track to
restrict use of sysadmin. If users need to create objects, have them
do so on a test server, not the production server, and then move those
objects to the production server. If this is something they do on a
regular basis, then consider instead creating those objects in advance
and simply moving data in and out of them.

2. fully-qualify the names of objects you are accessing in all Access
front-end forms, reports and queries to "select blah from dbo.mytable"
instead of "select blah from mytable" instead of relying on defaults.
This will make your code more explicit while optimizing performance as
it will save the server having to go hunt for the objects' schema
name.

3. get comfortable using SQL Profiler so you can see the exact calls
going to the server. This will help you with troubleshooting and
debugging.

--Mary