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?
Best Answer
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:
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.
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.