Database user password change in Azure SQL after Import bacpac

Question

On Prem:\

I have a db called db1
User test is having access to db1
Now I have exported bacpac

in Azure SQL Single DB:

Import bacpac file
Able to Connect with server admin and able to see db1 in Azure.

Issue: I wanted to connect with db user called Test and it is failed with login failure. I tried to reset the password and got below error. The parameter PASSWORD cannot be provided for users that cannot authenticate in a database.

Please help me, instead of dropping this db user and recreate any other way to make the connectivity with the test user? So that existing db objects will remain same in the database.

Please suggest

Answer 1

Hi @Prakash M, Bhanu ,

Here are the steps you need to take once you import the DB using backpack file.

Once you import the backpack file from on-prem to Azure SQL, your login(serverlevel) and users(DBlevel) will be created.

The backpac holds the information of users. The passwords of these users are changed in the background due to security compliance.

You will need to reset the password once the backpack file is imported.

 on Master   
      
 CREATE USER testuser FROM LOGIN testuser ;  -- since the SQL account won't exist on the master DB   
  
once the user is created on the master DB, then change the password using the below command   
  
on master   
  
ALTER login testuser WITH PASSWORD = 'PasswordHere'  

You no need to drop the users or re-map the permissions again. The only extra steps are 1) create user on the master 2) reset login password

Before you run the commands, you can check the DB users and roles using the below command on the user DB.

 SELECT p2.name AS UserName,  
 STRING_AGG(p1.name, ', ') AS DatabaseRoleName  
 FROM sys.database_role_members AS m  
 RIGHT OUTER JOIN sys.database_principals AS p1  
  ON m.role_principal_id = p1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS p2  
  ON m.member_principal_id = p2.principal_id  
 WHERE p2.name IS NOT NULL  
  AND p2.name NOT IN ('dbo')  
 GROUP BY p2.name  
 ORDER BY p2.name  

Hope this helps

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Please run these while connected to the master database on the Azure Logical Server.

CREATE LOGIN YourLogin WITH PASSWORD = 'YourPassword'  
  
CREATE USER YourLogin FROM LOGIN YourLogin ;  

After that run the following script on the imported database.

DROP USER YourLogin ;  
  
CREATE USER YourLogin FROM LOGIN YourLogin ;  

Hope that helps;