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
On Wed, 2 Sep 2009 13:22:31 -0400, "Watson, Rick"
<(E-Mail Removed)> wrote:
>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
|
Similar Threads
