Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I restored a database from one SQL Server instance on to a different SQL Server instance. The database allready has users added to it. But they don't have Login's for the new SQL Server instance.

I know how to create new logins for new users, but how do I create Login's for existing users?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

The problem is that the restore brings back the db users from the original server instance but the new instance typically knows nothing of those user logins. You'll see them under Security at the db level, but they don't have corresponding server level logins.

You need to reattach the database users with server logins on the instance on which you restored the database.

There are a couple of ways to go about it:

  1. Create new logins for them on the new instance. You'll then have to remove them as database users on the new instance and add their new logins. This seems odd given that you can create the same login names, but the SID's (security identifiers) will be different and that is what SQL uses for user identification. This is easy enough for one or two logins.

  2. If you want to maintain the same users, keeping the same SID's, across instances then use the sp_help_revlogin stored procedure. Copy the code from the link which will create the necessary stored procedures and run it on the server that you want to copy users from. It'll generate a SQL script that you can run on your target server to create the same users carrying over SID's, passwords, everything. This is the way to go if you have a lot of database users you need to reconnect on the target instance or if you don't know passwords to one or more SQL Logins on the source instance.

share|improve this answer

I always run the sp_change_users_login command with the 'Update_One' action to re-link the database user with the server login:

sp_change_users_login 'Update_One', 'database user', 'server login'

share|improve this answer

Here is what I found.

As squillman expained, the users get copied over with the database, but the logins do not. There is a store procdure called sp_change_users_login you can use to resolve the issue. The steps to do so are detailed in this aritcle. Details of using the store procdure can be found on this page.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.