Roles user BD Geo Azure without access error 916

Question

In SQL Azure, The roles of the users created in the main database are not replicated or copied to the geo database and generate error 916

enter image description here

Can you help me?

Could you please help me with a solution to this error, so that the user hosted in the GEO database can access his database with his role assigned in the main database.

Answer 1

The roles of the users created in the main database are not replicated or copied to the geo database.

To create and access users from primary data base to replicated database follow below steps.

First follow below steps on the master database of the Primary server:

• Create a new login with login name and password and also create a corresponding user and assign it to the dbmanager role (or any other role as per your requirement).

CREATE LOGIN [Login_name] WITH PASSWORD = 'secure-password' 

CREATE USER [User_name] FOR LOGIN [Login_name]
ALTER ROLE dbmanager ADD MEMBER [User_name] 

• Obtain the login's SID so that it may be recreated. only applies to logins using SQL Server authentication. note it down for later use using the command below:

SELECT sid FROM sys.sql_logins WHERE name = '[User_name]'

Now follow below steps on the source database of the Primary server:

• Create user for the same login as master database and add the user to the db_datareader role:

CREATE USER [User_name] FOR LOGIN [Login_name]
ALTER ROLE db_datareader add MEMBER [User_name]

Now, create login on the master database of the Secondary server:

• Using the same username, password, and SID from the user in the primary master database, create the same login as on the primary server:

CREATE LOGIN [Login_Name] with password = 'secure-password', sid=0x010600000000006400000000000000003C5857161B989940886580923D14E710

Output