none
How to transfer a database (mdf) from one computer to another

    Question

  • I've created a database on my desktop. Now I copied the mdf and ldf file to my laptop. I have a c# app that connects to this database and it connects fine when on my desktop. But when I attached the database to sql server studio on laptop, when I try to connect, an error appears. The error is somewhat like "cannot open database requested by the login. login failed by user 'xxx'". I both have windows authentication. What could be the problem?
    Sunday, August 02, 2009 3:34 AM

Answers

  • If I understand correctly then it is an issue with an "orphaned user".  SQL works on a layered security approach, the first layer being the instance of SQL, the second is the database.  Although you have the same user in both instance and database how SQL references these is not by name, consider this much like DNS as the web site we type in is meant so that we can remember this and associate it with the name, but it is in fact an ip address.

    In order to see if this is truly the problem then use sp_change_users_login to ascertain if this is causing the problem:

    --For mixed mode authentication sql users
     sp_change_users_login 'Auto_Fix', 'USER_NAME', NULL, 'PASSWORD';
    GO
    
    --For orphaned integrated users
    sp_change_users_login @Action = 'report';
    GO
    
    -- Generate list of orphaned users
    SELECT ROW_NUMBER() OVER(ORDER BY m.name) AS id, 
    	   m.name
    --INTO #temptbl
    FROM sysusers loc INNER JOIN sys.server_principals m 
     ON loc.name = m.name 
    WHERE loc.sid <> m.sid 
          AND type IN('S','U');
    
    
    DECLARE @liI INT,
            @liMax INT,
            @lcUserName NVARCHAR(256)
    
    SELECT @liI =MIN(id), 
    	   @liMax = MAX(id) FROM #temptbl
    
    -- iterate through list of orphaned users and fix
    WHILE @liI <= @liMax
    BEGIN
        SELECT @lcUserName = name 
    	FROM #temptbl 
    	WHERE [id] = @liI
        EXEC ('ALTER USER ' + @lcUserName + ' WITH LOGIN = ' + @lcUserName)
       SET @liI = @liI + 1
    END
    DROP TABLE [#temptbl]
    GO
    
    sp_change_users_login @Action='Report';
    GO
    
    The above script is used to fix both Integrated logins and mixed mode logins.  The MSDN on-line documentation for this system stored procedure is here:
    http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx

    Hope this helps

    David Dye
    • Marked as answer by manduz_griffus Wednesday, August 05, 2009 1:45 PM
    Sunday, August 02, 2009 3:02 PM

All replies

  • Make sure that your user has access to the database. Go to Security/Users under your database in Management Studio and make sure you grant proper access to the user you are using.

    Thanks,

    Varsham Papikian, New England SQL Server User Group Executive Board, USA: www.nesql.org; http://www.linkedin.com/in/varshampapikian
    Sunday, August 02, 2009 4:40 AM
  • Hi Varsham,

    Im not sure how to do that. But under the Users, there is already a dbo user whose login name is the laptop's name. If I try to create a new user using the laptop's name as user name and login name, it says that there is already a user associated with the login. Could you elaborate a bit on what you meant?
    Sunday, August 02, 2009 4:53 AM
  • If I understand correctly then it is an issue with an "orphaned user".  SQL works on a layered security approach, the first layer being the instance of SQL, the second is the database.  Although you have the same user in both instance and database how SQL references these is not by name, consider this much like DNS as the web site we type in is meant so that we can remember this and associate it with the name, but it is in fact an ip address.

    In order to see if this is truly the problem then use sp_change_users_login to ascertain if this is causing the problem:

    --For mixed mode authentication sql users
     sp_change_users_login 'Auto_Fix', 'USER_NAME', NULL, 'PASSWORD';
    GO
    
    --For orphaned integrated users
    sp_change_users_login @Action = 'report';
    GO
    
    -- Generate list of orphaned users
    SELECT ROW_NUMBER() OVER(ORDER BY m.name) AS id, 
    	   m.name
    --INTO #temptbl
    FROM sysusers loc INNER JOIN sys.server_principals m 
     ON loc.name = m.name 
    WHERE loc.sid <> m.sid 
          AND type IN('S','U');
    
    
    DECLARE @liI INT,
            @liMax INT,
            @lcUserName NVARCHAR(256)
    
    SELECT @liI =MIN(id), 
    	   @liMax = MAX(id) FROM #temptbl
    
    -- iterate through list of orphaned users and fix
    WHILE @liI <= @liMax
    BEGIN
        SELECT @lcUserName = name 
    	FROM #temptbl 
    	WHERE [id] = @liI
        EXEC ('ALTER USER ' + @lcUserName + ' WITH LOGIN = ' + @lcUserName)
       SET @liI = @liI + 1
    END
    DROP TABLE [#temptbl]
    GO
    
    sp_change_users_login @Action='Report';
    GO
    
    The above script is used to fix both Integrated logins and mixed mode logins.  The MSDN on-line documentation for this system stored procedure is here:
    http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx

    Hope this helps

    David Dye
    • Marked as answer by manduz_griffus Wednesday, August 05, 2009 1:45 PM
    Sunday, August 02, 2009 3:02 PM