Issues with Fully Qualified Table Access

I recently migrated Database1, which contains Table1, from one SQL Server
2000 box to another (Source and Dest, I'll call them). This is probably the
50th database migration I've performed within the past few months. My
general steps for migration are:
1. Create and execute the sp_help_revlogin stored procedure on Source to
copy all the SQL Server logins from Source to Dest.
2. Copy the sp_help_revlogin output and execute on Dest server.
3. Backup Database1 on Source, and restore Database1 on Dest.
4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
orphaned logins on Dest's Database1.
With Database1, there are many database objects, tables, created using the
login "User1." Here's where I'm stumped.
1. When I try to select from the User1-created Table1 on *SOURCE*, while I'm
logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get my resultset, no issues.
2. After the migration, when I try to select from the User1-created Table1
on *DEST*, while I'm logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
Invalid object name 'TABLE1'" and *no resultset*.
At least I'm relieved then when I try fully qualifying the table
reference:
SELECT * FROM USER1.TABLE1
.... I get my resultset back with no error messages.
3. Next, I try assigning User1 to the System Administrators server role in
Database1 on *DEST* to try to find a way around the issue above. No dice--
same error message. Also, viewing the Users choice under Database1 on
*DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
"sp_change_users_login 'Update_One', 'User1', 'User1'" returns the message,
"The number of orphaned users fixed by updating users was 0." Also, trying
to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
Database1, gives me "Error 15183: The user owns objects in the database and
cannot be dropped." (I was experimenting to see if I can drop and re-add
user.)
The problem is-- application dependencies exist where the application
connects to the database server as User1, and appears to *not* fully qualify
table access. This is a legacy application that's existed without
modification for years and I'm afraid can't be modified to have qualified
table access.
However, I'm stumped by the issue. I'm wondering what variables and
configuration settings I haven't considered yet that, on SOURCE, would allow
me to *not* qualify table access and be OK with it, yet on DEST, seem to
require table qualification.
Any help and assistance would be greatly appreciated in advance.
Thanks,
- H-
H Lee,
First, true confession: I did not read the whole thing and attempt to
understand it all. However...
Your process of backup and restore has a common flaw. This can result in
the restored database having a different owner of the database from the
internal user mapped as dbo. If this is your case, then you should do the
following:
sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
If this works, then great.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>I recently migrated Database1, which contains Table1, from one SQL Server
> 2000 box to another (Source and Dest, I'll call them). This is probably
> the
> 50th database migration I've performed within the past few months. My
> general steps for migration are:
> 1. Create and execute the sp_help_revlogin stored procedure on Source to
> copy all the SQL Server logins from Source to Dest.
> 2. Copy the sp_help_revlogin output and execute on Dest server.
> 3. Backup Database1 on Source, and restore Database1 on Dest.
> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
> orphaned logins on Dest's Database1.
> With Database1, there are many database objects, tables, created using the
> login "User1." Here's where I'm stumped.
> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
> I'm
> logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get my resultset, no issues.
> 2. After the migration, when I try to select from the User1-created Table1
> on *DEST*, while I'm logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
> Invalid object name 'TABLE1'" and *no resultset*.
> At least I'm relieved then when I try fully qualifying the table
> reference:
> SELECT * FROM USER1.TABLE1
> ... I get my resultset back with no error messages.
> 3. Next, I try assigning User1 to the System Administrators server role in
> Database1 on *DEST* to try to find a way around the issue above. No
> dice--
> same error message. Also, viewing the Users choice under Database1 on
> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
> message,
> "The number of orphaned users fixed by updating users was 0." Also,
> trying
> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
> Database1, gives me "Error 15183: The user owns objects in the database
> and
> cannot be dropped." (I was experimenting to see if I can drop and re-add
> user.)
> The problem is-- application dependencies exist where the application
> connects to the database server as User1, and appears to *not* fully
> qualify
> table access. This is a legacy application that's existed without
> modification for years and I'm afraid can't be modified to have qualified
> table access.
> However, I'm stumped by the issue. I'm wondering what variables and
> configuration settings I haven't considered yet that, on SOURCE, would
> allow
> me to *not* qualify table access and be OK with it, yet on DEST, seem to
> require table qualification.
> Any help and assistance would be greatly appreciated in advance.
> Thanks,
> - H-
>
>
>
|||Russell,
I appreciate your effort and reply. I was wondering if you could explain
how I might eliminate the initial backup and restore flaw.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>H Lee,
> First, true confession: I did not read the whole thing and attempt to
> understand it all. However...
> Your process of backup and restore has a common flaw. This can result in
> the restored database having a different owner of the database from the
> internal user mapped as dbo. If this is your case, then you should do the
> following:
> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
> If this works, then great.
> RLF
>
> "H Lee" <anon@.anon.com> wrote in message
> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>
|||If what I described is actually the problem (and I do not know that it is)
then there is little that you can do about it. By design, SQL Logins are
independent between servers. If the owner of the databases is a domain
user, then the SID should be the same, but I have not played with it to
determine what other issues arise.
1 - If the database to which you restore was created with the wrong owner,
you may need to do the reset steps only once. Then, once set db is owned by
the proper account, there will be no unsynchronized dbo user inside the
database.
2 - If that does not work, include the resetting of the owner during the
restore process (script restore followed by the code to fiddle with the
owner).
3 - If the owner of the database on both servers is 'sa' then that might be
sufficient to avoid the problem since the 'sa' account always has the same
id. (But your application, like some I have seen, may demand that the owner
of the database be an application specific owner.)
It is not really a *** problem ***, just a consequence of two independent
machines.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:OXb5bSr%23FHA.2464@.TK2MSFTNGP15.phx.gbl...
> Russell,
> I appreciate your effort and reply. I was wondering if you could explain
> how I might eliminate the initial backup and restore flaw.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>
sql