Using the copy database wizard

[Mr Gabriel]

Hi to all,
I'm trying to use the copy database wizard to copy a database from the
2000 version to the 2005 version of sql server. I choose not to copy
logins so I expect to find a database with only an administrative login
o none at all. When I start the created package it throws an error
saying:

errorCode=-1073548784 description=Executing the query "EXEC
dbo.sp_grantdbaccess @loginame = N'PresIng1', @name_in_db = N'PresIng1'
" failed with the following error: "'PresIng1' is not a valid login or
you do not have permission.". Possible failure reasons: Problems with
the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.

I told the wizard NOT to transfer logins. What is it doing?

In the origin engine there's mixed mode auth (win + sqlserver) and in
destination engine there's only win auth: can it be an issue?

Thanks,
Gabriele

[Erland Sommarskog]

Mr Gabriel (gz******@racine.ra.it) writes:

I'm trying to use the copy database wizard to copy a database from the
2000 version to the 2005 version of sql server. I choose not to copy
logins so I expect to find a database with only an administrative login
o none at all. When I start the created package it throws an error
saying:

errorCode=-1073548784 description=Executing the query "EXEC
dbo.sp_grantdbaccess @loginame = N'PresIng1', @name_in_db = N'PresIng1'
" failed with the following error: "'PresIng1' is not a valid login or
you do not have permission.". Possible failure reasons: Problems with
the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.

I told the wizard NOT to transfer logins. What is it doing?

It seems that it migrates database users. This is not the same as logins.
Logins have access on server level, users on database level.

Note that it very well could make sense to copy users, even if you are
not copying logins, as the logins could already exist at the target server.

Look for an option to not migrate users.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx