Sync login with SID in Azure SQL DB

Question

Hi ,

I have restored the Azure PROD DB copy to Azure QA DB

In Azure QA master DB already contains users

Now how to sync login SID's from master db to QA user DB?

Answer 1

Let's say you have created a login on the master database named TestUser as shown below"

---- ON Master Level  
CREATE USER TestUser  
    FOR LOGIN TestUser  
    WITH DEFAULT_SCHEMA = dbo  

To create that login as a user on the QA database, connect to the QA database and run the following query:

---- ON SQL DB LEVEL  
CREATE USER TestUser  
    FOR LOGIN TestUser  
    WITH DEFAULT_SCHEMA = dbo  
GO  

After that grant the user appropriate permissions to objects.

GRANT SELECT ON OBJECT::dbo.Sales TO TestUser;  
GO  

You can see the mapping between SIDs if you do as explained here.