1

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?

3
  • 'I could not map it to any login' - why? Is there a login user missing or what? – dezso Aug 29 '13 at 8:18
  • 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 Aug 29 '13 at 8:38
  • use DB ALTER USER U1 WITH NAME = U1 , LOGIN = L1 – Paul Stanley Aug 29 '13 at 14:28
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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