locked
transfer logins can't find database? RRS feed

  • Question

  • Trying to transfer logins from SQL Server 2005 to SQL Server 2008 R2. Everything looks good until execution. Then it throws an error saying that it can't find the source database. (The databases don't exist ... yet ... in the destination server.)

    It finds the tables just fine when I am selecting which tables to use to get a list of user logins. How come it can't find the tables when it's time to do the work?

    I'm using Windows Integrated Authentication, and have sysadmin privileges on both instances.

    Wednesday, February 29, 2012 9:26 PM

Answers

  • It looks like it is complaining that the destination database(s) the login(s) use does not exist or is not available. Possibly it has something to do with the default database setting for the login you are trying to transfer, not existing on the destination instance. 

    If you cannot get it to work, possibly look at using this an alternative http://support.microsoft.com/kb/918992

    • Marked as answer by ppmichael Monday, March 5, 2012 3:15 PM
    Thursday, March 1, 2012 1:21 AM
  • You need to create the Payroll database on the destination server before you can transfer logins.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    • Marked as answer by ppmichael Monday, March 5, 2012 3:11 PM
    Monday, March 5, 2012 3:39 AM

All replies

  • It looks like it is complaining that the destination database(s) the login(s) use does not exist or is not available. Possibly it has something to do with the default database setting for the login you are trying to transfer, not existing on the destination instance. 

    If you cannot get it to work, possibly look at using this an alternative http://support.microsoft.com/kb/918992

    • Marked as answer by ppmichael Monday, March 5, 2012 3:15 PM
    Thursday, March 1, 2012 1:21 AM
  • Please provide the full error message and let us know what tool you're using to transfer the logins.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Thursday, March 1, 2012 1:26 AM
  • I am using SSIS 2008 R2 (thought that's where I posted this! but got here by searching the issue).

    The error is:

    -----
    [Transfer Logins Task] Error: Execution failed with the following error: "The database 'Payroll' does not exist. Supply a valid database name. To see available databases, use sys.databases. ".
    -----

    The databases exist on the source server, but not the destination server. Maybe what I need to do is copy a database over first. I'll test that.

    Friday, March 2, 2012 2:50 PM
  • You need to create the Payroll database on the destination server before you can transfer logins.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    • Marked as answer by ppmichael Monday, March 5, 2012 3:11 PM
    Monday, March 5, 2012 3:39 AM
  • So my testing demonstrated.

    Thanks.

    Monday, March 5, 2012 3:11 PM
  • Correct - destination db did not exist.

    It does not rely on the destination db having the same db users as are mapped to the logins it is transferring - just that there be a db in destination of the same name.

    Since it is transferring logins only and has no dependency on the db users existing, it isn't obvious that there would be any dependency on the destination db existing. But there is.

    Thanks.

    Monday, March 5, 2012 3:17 PM