COPY database wizard error

When I try to copy a SQL Server 2005 database to the same server, the Copy
database wizard always gets an error at the very end. It says "User
'abcuser' does not exist or you don't have permissions". 'abcuser' is a SQL
Server user that we no longer use because it wouldn't work for our
application anymore once we restored into SQL Server 2005 from SQL Server
2000. We can't delete 'abcuser' because its says it is an owner. We can't
change the owner to another user either. The copy process yields a new copy
of the database, BUT we can't see any tables, can't add users, etc. because
it says we don't have premissions. Throughout the COPY database wizard we
used Windows Authentication when asked.
Question: is there a known bug when restoring from SQL Server 2000 in terms
of users? Is there any way to copy this database even though it has this
rogue user?
Thanks!Hi Dean
"Dean J Garrett" wrote:

> When I try to copy a SQL Server 2005 database to the same server, the Copy
> database wizard always gets an error at the very end. It says "User
> 'abcuser' does not exist or you don't have permissions". 'abcuser' is a SQ
L
> Server user that we no longer use because it wouldn't work for our
> application anymore once we restored into SQL Server 2005 from SQL Server
> 2000. We can't delete 'abcuser' because its says it is an owner. We can't
> change the owner to another user either. The copy process yields a new cop
y
> of the database, BUT we can't see any tables, can't add users, etc. becau
se
> it says we don't have premissions. Throughout the COPY database wizard we
> used Windows Authentication when asked.
Why can't you change the owner?
It sounds like you are not a sysadmin on the SQL 2005 system?

> Question: is there a known bug when restoring from SQL Server 2000 in term
s
> of users? Is there any way to copy this database even though it has this
> rogue user?
Have you tried detatch/attach or backup/restore?

> Thanks!|||Dean J Garrett wrote:
> When I try to copy a SQL Server 2005 database to the same server, the Copy
> database wizard always gets an error at the very end. It says "User
> 'abcuser' does not exist or you don't have permissions". 'abcuser' is a SQ
L
> Server user that we no longer use because it wouldn't work for our
> application anymore once we restored into SQL Server 2005 from SQL Server
> 2000. We can't delete 'abcuser' because its says it is an owner. We can't
> change the owner to another user either. The copy process yields a new cop
y
> of the database, BUT we can't see any tables, can't add users, etc. becau
se
> it says we don't have premissions. Throughout the COPY database wizard we
> used Windows Authentication when asked.
> Question: is there a known bug when restoring from SQL Server 2000 in term
s
> of users? Is there any way to copy this database even though it has this
> rogue user?
> Thanks!
>
There's a simple fix for the 'abcuser' problem that started this whole
mess. Go to the original server, look at the syslogins table, and make
note of the "sid" that is assigned to the 'abcuser' login. Create a
login on your new server, named 'abcuser', and assign it the same "sid"
that it had on the original server. This will resolve the original
problem that you had after restoring the database.
Once that's resolved, stop using the Copy Database Wizard. If you want
to clone a database, do a backup/restore, making sure to use the WITH
MOVE option on the restore command to create new data files for the
restored database. Much faster and more straightforward than that
wizard contraption.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsqlsql