Sample Header Ad - 728x90

AAD users lose access to database after import

0 votes
0 answers
127 views
I googled for the last few days but I got a bit confused and I do not know how to proceed further. Also, I'm not a DBA and I was just faced with this situation for the first time. Using sqlpackage.exe I'm exporting a bacpac from a PRD DB and import it on another server to refresh the lower environments. The original database is contained. After I'm importing the new database, the contained database users and the AAD external ones lose access to the new database. I'm fixing the contained database user by updating its password with
ALTER USER [RandomUser] WITH PASSWORD='randomPassword'
but I am not sure what to do with the AAD users. They have an external user created for them (the aad users are placed inside a group). So far my option is to drop the said user and recreate it
CREATE USER [sthRandom] FROM EXTERNAL PROVIDER
but in order to do this, you have to be connected with an AAD user
Principal 'sthRandom' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.
I have to automate this whole process so I could use a service principal like [this](https://techcommunity.microsoft.com/t5/azure-sql/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467) . I'm creating the service principal inside all the databases, but after refresh the user won't exist anymore, or I create it in the PRD database too, but I can not use it to login anymore. What other options do I have? Thanks
Asked by Roxananana (1 rep)
May 25, 2021, 02:47 PM
Last activity: May 25, 2021, 02:48 PM