I am trying to transfer one SQL database to another. I decided to test internally first and I can't get this to work.
If I am logged in as sa, I can use the import wizard in SQL Server 7 to successfully transfer any database to another database. However, if I create a new database called testdb and then create a user called testuser and make testuser the dbo of testdb and then try to use the import wizard to import data from another database and select to "Transfer objects and data between SQL Server 70 databases" that both exist on the same machine (using sa to connect to source database and testuser to connect to destination databasea), I get errors in SQL saying the following:
Window Title: "Transfer SQL Server Objects"
Error Message: "[Microsoft][ODBC SQL Server Driver][SQL Server] Only members of the sysadmin role or the database owner may set database options."
I don't know what database options are trying to be set or why I would get this error since I am logged in as dbo for the testdb destination database and sa for the source database.
I have tried this same procedure with the following selections turned on and off, but I get the same error either way.
"Transfer database users and database roles", "Transfer SQL Server Logins", "Transfer Object-Level Permissions"
Basically, what I am I doing wrong? Is there a better way to do this? I am trying to test this process internally so that I will be able to use the same set of steps eventually to transfer a database developed internally to a hosting company.