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...
>

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

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...
>>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-
>>
>>
>|||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...
>>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-
>>
>>
>>
>

Issue with distinct count

I have a table which contains activities, and I am trying to extract a section that has less than a certain number of activities for a given date range. I am receiving an error of

Incorrect syntax near 'activityid' for my select clause. Any help would be greatly appreciated.

Code Snippet

Query I'm working with
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare @.sectionid int, @.maxcount int, @.stdate datetime, @.enddate datetime
set @.sectionid = 37
set @.maxcount = 5
set @.stdate = '01/01/06'
set @.enddate = '01/31/06'

select distinct (count activityid) <= @.maxcount, a.sectionid
from activity a
where
a.sectionid = @.sectionid
AND
(a.date between @.stdate and @.enddate)

Table Definition
>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE PROCEDURE [dbo].[p_Activity_Create]
(
@.ActivityID INT OUTPUT,
@.SectionID INT,
@.ActivityCategoryID INT,
@.STIAssessmentTestID INT,
@.Name VARCHAR(30),
@.Date DATETIME,
@.MaximumScore DECIMAL(6,2),
@.WeightAddition DECIMAL(9,6),
@.WeightMultiplier DECIMAL(9,6),
@.IsAssessment BIT,
@.IsHomework BIT,
@.IsScored BIT,
@.MayBeDropped BIT,
@.IsDropped BIT,
@.IsComplete BIT,
@.Unit VARCHAR(50)

)
AS

SET NOCOUNT ON

INSERT INTO [dbo].[Activity]
(SectionID,
ActivityCategoryID,
STIAssessmentTestID,
[Name],
[Date],
MaximumScore,
WeightAddition,
WeightMultiplier,
IsAssessment,
IsHomework,
IsScored,
MayBeDropped,
IsDropped,
IsComplete,
Unit)
VALUES
(@.SectionID,
@.ActivityCategoryID,
@.STIAssessmentTestID,
@.Name,
@.Date,
@.MaximumScore,
@.WeightAddition,
@.WeightMultiplier,
@.IsAssessment,
@.IsHomework,
@.IsScored,
@.MayBeDropped,
@.IsDropped,
@.IsComplete,
@.Unit)

SET @.ActivityID = SCOPE_IDENTITY()
GO

Table data
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

ActivityID SectionID Name Date ActivityCategoryID STIAssessmentTestID MaximumScore WeightAddition WeightMultiplier IsAssessment IsHomework IsScored MayBeDropped IsDropped IsComplete 11 37 Homework 9/20/2005 49 NULL 25 0 1 0 1 1 1 0 0 14 37 Midterm 10/16/2005 51 NULL 100 0 1 1 0 1 1 0 0 38 37 Homework 1/16 1/16/2006 49 NULL 25 0 1 0 1 1 1 0 0 39 37 Homework 1/17 1/17/2006 49 NULL 25 0 1 0 1 1 1 0 0 40 37 Extra 1/18 1/18/2006 52 NULL 50 0 1 0 0 1 1 0 0 41 37 Homework 1/19 1/19/2006 49 NULL 25 0 1 0 1 1 1 0 0 12 37 Quiz 1/20/2006 50 NULL 50 0 1 0 0 1 1 0 0 13 37 Test 4/20/2006 51 NULL 100 0 1 0 0 1 1 0 0 15 37 Final 5/20/2006 51 NULL 100 0 1 1 0 1 1 0 0 1 42 Homework 9/20/2005 5 NULL 25 0 1 0 1 1 1 0 0 4 42 Midterm 10/16/2005 7 NULL 100 0 1 1 0 1 1 0 0 2 42 Quiz 1/20/2006 6 NULL 50 0 1 0 0 1 1 0 0 3 42 Test 4/20/2006 7 NULL 100 0 1 0 0 1 1 0 0 5 42 Final 5/20/2006 7 NULL 100 0 1 1 0 1 1 0 0 26 43 Homework 9/20/2005 85 NULL 25 0 1 0 1 1 1 0 0 29 43 Midterm 10/16/2005 87 NULL 100 0 1 1 0 1 1 0 0 27 43 Quiz 1/20/2006 86 NULL 50 0 1 0 0 1 1 0 0 42 43 Homework 4/17 4/17/2006 85 NULL 25 0 1 0 1 1 1 0 0 43 43 Extra 4/18 4/18/2006 88 NULL 50 0 1 0 0 1 1 0 0 44 43 Homework 4/19 4/19/2006 85 NULL 25 0 1 0 1 1 1 0 0 28 43 Test 4/20/2006 87 NULL 100 0 1 0 0 1 1 0 0 45 43 Extra 4/21 4/21/2006 88 NULL 50 0 1 0 0 1 1 0 0 30 43 Final 5/20/2006 87 NULL 100 0 1 1 0 1 1 0 0 16 49 Homework 9/20/2005 61 NULL 25 0 1 0 1 1 1 0 0 19 49 Midterm 10/16/2005 63 NULL 100 0 1 1 0 1 1 0 0 17 49 Quiz 1/20/2006 62 NULL 50 0 1 0 0 1 1 0 0 18 49 Test 4/20/2006 63 NULL 100 0 1 0 0 1 1 0 0 20 49 Final 5/20/2006 63 NULL 100 0 1 1 0 1 1 0 0 21 50 Homework 9/20/2005 65 NULL 25 0 1 0 1 1 1 0 0 24 50 Midterm 10/16/2005 67 NULL 100 0 1 1 0 1 1 0 0 22 50 Quiz 1/20/2006 66 NULL 50 0 1 0 0 1 1 0 0 23 50 Test 4/20/2006 67 NULL 100 0 1 0 0 1 1 0 0 25 50 Final 5/20/2006 67 NULL 100 0 1 1 0 1 1 0 0 31 53 Homework 9/20/2005 121 NULL 25 0 1 0 1 1 1 0 0 34 53 Midterm 10/16/2005 123 NULL 100 0 1 1 0 1 1 0 0 32 53 Quiz 1/20/2006 122 NULL 50 0 1 0 0 1 1 0 0 33 53 Test 4/20/2006 123 NULL 100 0 1 0 0 1 1 0 0 35 53 Final 5/20/2006 123 NULL 100 0 1 1 0 1 1 0 0 6 67 Homework 9/20/2005 33 NULL 25 0 1 0 1 1 1 0 0 9 67 Midterm 10/16/2005 35 NULL 100 0 1 1 0 1 1 0 0 7 67 Quiz 1/20/2006 34 NULL 50 0 1 0 0 1 1 0 0 8 67 Test 4/20/2006 35 NULL 100 0 1 0 0 1 1 0 0 10 67 Final 5/20/2006 35 NULL 100 0 1 1 0 1 1 0 0

43 row(s) affected)

The problem is with the line:

select distinct (count activityid) <= @.maxcount, a.sectionid

To me this looks like pseudocode.

You might try something more like:

select sectionId,
count(distinct activityId)
from activity a
where a.sectionId = @.sectionId
and a.date between @.stDate and @.endDate
group by sectionId
having count(distinct activityId) <= @.maxCount