Conflict with logins/usres when Importing databases

Hi,

We have imported several databases from an old SQL Server 2000 deployment in a SQL Server 2005 instance. After the installations we have check that the import doesn't create the logins associated with the users nad now it seems impossible to create "manually" the login. What should be do? Re-import the database after having created the login?

Thanks
You should still be able to create the login using CREATE LOGIN and then run the system proc
sp_change_users_login to map the existing users to the newly created login.

Reimporting the database will probably not work as the SIDs of the new logins will not match those of the old logins.

HTH.
|||Effectively, we have been able to create the login but we are unable to associate it to the user using the the "User Properties". The field "Login" appears empty and disabled, so there is no way to assign a user from this screen.

Reading your answer I understand that I can map the users to the newly created logins using the sp_change_users_login function. It's true? Could you send me a sample of use of this function? How should we execute it? (We are SQL Server newbies coming from Oracle world......)+

Thanks
|||

I assume you imported only your user-database probably using import wizard or something. Security information (users and logins) is stored in 'master' database in SQL Server world. You have to transfer these information from your old database to new one.

Check this out.

How to transfer logins and passwords between instances of SQL Server

http://support.microsoft.com/kb/246133/en-us

|||sample:

sp_change_users_login 'AUTO_FIX', 'some_login'

|||Check the information in Books Online which will give you a full explanation of the options open to you:

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

Essentially to map an existing user Bob to a new login of Bob you're looking at running:

sp_change_users_login 'Update_One', 'Bob', 'Bob';

Hope this helps;