Transfer Database Task error

[organicg]

I'm trying to transfer a SQL2000 DB from my webhost to my local box that has SQL2005 installed. I opened a SSIS project and created a Transfer Database Task. Configured it to do the transfer and ran it in debug.
I get the error "User, group, or role 'skcar' already exists in the current database."

This seems to make no sense because the local database was just created by the Transfer DB Task, unless the message applies to the remote DB which makes even less sense because why would any object be created on the remote server?

What am I doing wrong?

FYI, on first attempt, I got a schema error(I forget exactly). There was one table that was owned by "skcar", not "dbo", so I ran sp_changedbobjectowner on it and changed it. There's still 3 Views that are owned by "skcar". I could change them if need be.
Geez, tough for a developer to really understand DB stuff.

 

[organicg]

I tried executing the .dtsx package in the Execute Package Utility. The above error is happening on:
EXEC dbo.sp_grantaccess @loginname=N'skcar', @name_in_db=N'skcar' and it says possible failure reasons: problems with the query, ResultSet property not set correctly, or connection not established correctly.

Thanks for the help.

 

[Catadmin]

If the user name for your database already exists on the new server for a different database, that's probably your issue. Try renaming the user account on one of the databases and doing the Transfer Database task again.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[organicg]

Yes, the database user already existed in another local DB. I successfully ran "alter user skcar with skcars" however I still get the "sp_grantaccess" error.

 

[organicg]

I deleted the database that already had user 'skcar' so that 'skcar' only existed as a server login, but not a specific database user and I still get the same error.

 

[Catadmin]

Drop the server login (and all other server logins that exist on server 2 and exist in the database you're moving), run the Transfer Databases task again, then run a sp_Change_Users_Login to autofix the logins so they now have both DB & Server access.

This should resolve your problem.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[organicg]

I dropped the 'skcar' server login that was the same name as the database user, then ran the Database Transfer Task.

It errors on EXEC dbo.sp_grantaccess @loginname=N'skcar', @name_in_db=N'skcar' "skcar is not a valid login or you do not have permission.

So I create server login 'skcar' again and run DTTask.

It errors on the same proc. and says "user, group, or role skcar already exists in the current database"

skcar DOES exist as a user in the remote DB, that's the user being used currently, I can't delete it. So why does the DTT try to create a new user from the local skcar server login?

 

[Catadmin]

It's the nature of the beast, unfortunately. It has something to do with orphaned users and I believe Microsoft did it on purpose for security reasons.

Sounds like you're going to have to remove both the login and the EXEC sp_grantaccess code in order to get your move to complete successfully. After moving the db, run sp_Change_Users_Logins proc, then run & readd the EXEC sp_GrantAccess code.


Sorry it took me so long to respond. I was out of town on vacation.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[organicg]

How can I remove EXEC sp_grantaccess from the Database Transfer Task, I thought it was built in to that task?

 

[organicg]

On

http://msdn2.microsoft.com/en-us/library/ms141204.aspx

, I found this:
"To transfer a database using online mode, the user who runs the package must be a member of the sysadmin server role or the database owner (dbo) of the selected database."

My user 'skcar' is a member of database role 'db_owner', but is not the actual 'dbo' user(I guess that's different than their requirement) nor is it a member of the sysadmin server role because this is a shared web hosting environment.

Is that my problem?? The error certainly doesn't tell me that.

 

[Catadmin]

Oh. I thought the Exec sp_grantaccess was part of a job or some other user-created object in your current database. (I have several of those in my db). That's why I told you to drop it from the db.

skcar isn't your login, is it? And yes, dbo and db_owner are two different things.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[organicg]

This is simply(ha!) an SSIS Database Transfer Task, reread my first postings.

Yes, skcar is the login of the source database, and it no longer exists as a server login on the destination server.

So do you think the MSDN post identifies the problem, I just can't do it without being dbo? I can try to just transfer the data.

 

[Catadmin]

Just an FYI: A database transfer task takes all the objects in it, including user created functions and stored procedures, when it transfers. If one of those UDFs or SPs has the command written into it, that could be a reason why the task isn't working.

I think you misunderstood what I meant when I asked about 'skcar'. Is 'skcar' the login YOU, yourself, use when you log into the network is what I meant.

And finally, I've been assuming you're sysadmin all along. Sorry about that. I should have asked that straight off. If you are neither sysadmin nor dbo, then yes, I believe the article does address the source of your problem. db_owner is just a role. The dbo is the actual, physical, owner of the database object.

I am really sorry I didn't ask that question off the bat. It would have saved you a lot of headaches. Do you have someone else who has sysadmin access? If so, they can transfer ownership of the DB to you or do the package themselves.

If not, you can still do a backup of the db, move the backup file to the new server and restore it from the backup.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[organicg]

troubleshooting via email is a tough way to communicate, and thanks for all your help.

I'm logged in to my box as an administrator and created the .dtsx in Visual Studio. I log in to SQL Server with Windows auth, though I don't know what privileges the .dtsx runs under when I run it in the Execute Package Utility.

FYI, this is pretty simple, small DB. I don't call any system procs from my procs, nor do I have any UDFs.

This is the typical shared hosting situation. I have an SQL login that they give me. I can login to the server, and my DB with it thru SQL2005, and I can see it's a member of db_owner, but it is skcar, not dbo. They say you must use DTS to backup/move data, they don't provide backups.
I will plead with them to let me login as dbo, though it would be nice if the original error told me if that was my problem. Otherwise, I'm left with figuring out some sort of actual data transfer task.
So frustrating that I'm trying to do something so simple.

 

[davidgdh]

I have had the same problem wanting to copy a DB from a shared service to a local server. I have had similar frustrations and spent long hours. The only solution I have found is to use DTS in sql2000 which is so much easier to understand and so much less picky than SSIS. Copied db to local sql2000 then used SSIS to copy again from sql2000 to 2005.

The DTS will fail any step that it thinks has invalid references, eg if you have a stored procedure referenced inside a view you have to copy the sp before the view itself.

So it turned out a rather manual affair and the whole thing only feasible because I need to do it once. And disappointing because I want to ditch 2000 and work only on 2005, but unless you are sysadmin on the source db 2005 will not let you play, using any of the tools that I was able to try at least. Soon as you are sysadmin as in my second step its fine, took a while but went through first time.

 

[organicg]

Ahh, I've got it!! I didn't notice that there was still an Import/Export Wizard accessible in various ways, one of which is to just rt-click on your DB-->Tasks-->Import Data. Can't believe I didn't notice that. It created the tables(and populated them), procs, and views. That's good enough for me.

http://www.databasejournal.com/features/mssql/article.php/3580216

For a more robust tool, I saw a recommendation for SQL Compare and SQL Data Compare from

http://www.red-gate.com/

, though I downloaded the 14-day trial and got errors and it failed to copy the DB.

Now to figure out why I can't upgrade my VS03 web project to VS05.