User mapping problem during deployment

Question

I have a development machine with a new database I have designed. The server has a login L1 and the database has a user U1. L1 is mapped to M1 and tables have permissions set up for U1. Authentication is SQL Server authentication. I wanted to fully backup the database and restore to the production server so that I could start to use it in production.

I had a problem with user U1: I could not map it to any login on the production server. So I could not use U1 with any login. Is this correct behavior?

• András Váczi: 'I could not map it to any login' - why? Is there a login user missing or what?
• Paul Stanley: Well the restored database on the production server has a user U1 and tables have permissions set for this user. But I cant map a login to this user OR I don't know how to.
• Paul Stanley: use DB ALTER USER U1 WITH NAME = U1 , LOGIN = L1

Answer 1

You have to create a login on the prod server as when you restore the backup to PROD server, you are having an orphaned user.

So steps are :

• Create Login using SID option (if you are just having 1 login to map). Else use sp_help_revlogin
• You can use SID from sys.database_principals where type_desc = SQL_USER (since you mentioned you are using SQL Login).

• Then run ALTER USER as below :

  USE [DatabaseName]
  go
  ALTER USER [UserName]
  WITH LOGIN = [UserName]
  

Note: sp_change_users_login is deprecated.