login problems after database move

I'm trying to move a darabase from one sql2005 server to another sql2005 server within the same domain.

I have tried both backup/restore and attach/dettach and get the same problem.

With this database I'm using a SQL Server user, the user name is on both servers with exactly the same password.

On the new sql 2005 server after I have moved the database to it, if I goto security->Users and do properties on the username. It says the user is set to "Without login" how do I change this to "Login name" ?

thanks,

martin

Hi there,

I believe, from your description, that you haven't resolved the user logins after restoring your database backup to the new server.

You can read more about this in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;240872

You can fix your problem by following the instructions in the article but I'm getting the impression from your post you only want to fix a single user (which is a SQL Server account & not a Windows domain account) so it might be quicker to use the "sp_change_users_login" stored procedure.

You can find more info on the stored proc in Books Online, but here's a link to it on the web:
http://msdn2.microsoft.com/en-us/library/ms174378.aspx

I believe that the syntax you have to use is:

EXEC sp_change_users_login @.Action = 'Update-One', @.userNamePattern = '<Database User Name>', @.login = '<SQL Server Login>'

Hope that helps a bit but sorry if it doesn't
|||

Thanks problem solved.

martin