Easy Question Hopefully

This happens in both SQL Server 2000 and SQL Server 2005

Database name: STAPLE

Owner: Scott

Table: ScottTab

I could login as Scott and state: Select count(1) from ScottTab; and results would be returned because the table ScottTab was associated to Scott and I did not have to prefix the table

It was backed up and restored to another SQL Server.

I tried the same query and am receiving the error table does not exist. I have tried to run: exec sp_changedbowner 'SCOTT' and exec sp_change_users_login 'Change_One','SCOTT','SCOTT' as well and get errors

Please let me know the proper steps I need to take to re-associate the new sql server engine to the restored database. The user SCOTT was setup the same way on both machines

When you say the user was setup the same way, does that include the SID? If you have SQL logins on two servers and want to be able to move databases between them with backup/restore or detach/attach, you should synchronize the logins by making sure they have the same SID value. The users in a database are matched to logins by SID, not by name.

Both sp_addlogin (2000) and CREATE LOGIN (2005) have an optional SID parameter that will help solve your problem.

|||There are other databases that already exist on the server so the user can not be dropped or re-created at the global level. I am also restoring databases from different servers. Any other ways to get around this problem?|||User exists at the database level and is mapped to a sql login. It's certainly okay to drop a user from a database without affecting other databases.

You're indeed seeing orphan user. Sp_change_users_login 'Update_One' should fix it.
Perhaps, you should run Sp_change_users_login 'Report' to see if there is an orphan.sql