I wish to transfer data between two different databases on the same Azure SQL server (not managed instance). I have an admin account. When i goto setup Data Sync within the Azure portal i cannot see the tables after i created the Sync Group.
I followed the instructions here https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell&view=azuresql#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities. The error i get is
SqlError Number:18456, Message: Login failed.
Ill use the following as examples
MasterDatabaseName = Master
HUBDatabaseName = DatabaseSend
ChildDatabaseName = DatabaseRecieve
Azure SQL Server Admin = [email protected]
However this is where i think the error lies, I login to the Azure Data Portal as [email protected] and i created the Server and databases with this company email login, however if i look in the overview inside the Azure Portal of the SQL server my server admin name is CloudS******** where * = random letters and numbers
If i do a assigned roles check on my databases using the following code
SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName FROM sys.database_principals AS u LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id WHERE u.type NOT IN('R', 'G') ORDER BY UserName , RoleName;
I get the Following for Master
| UserName | UserType | RoleName |
|---|---|---|
| CloudS******** | SQL_USER | dbmanager |
| CloudS******** | SQL_USER | loginmanager |
| [email protected] | EXTERNAL_GROUP | dbmanager |
| [email protected] | EXTERNAL_GROUP | loginmanager |
| dbo | SQL_User | db_owner |
DatabaseSend
| UserName | UserType | RoleName |
|---|---|---|
| [email protected] | EXTERNAL_USER | db_ddladmin |
| [email protected] | EXTERNAL_USER | db_backupoperator |
| [email protected] | EXTERNAL_USER | db_accessadmin |
| [email protected] | EXTERNAL_USER | db_securityadmin |
| [email protected] | EXTERNAL_USER | db_owner |
| dbo | SQL_User | db_owner |
DatabaseRecieve
| UserName | UserType | RoleName |
|---|---|---|
| [email protected] | EXTERNAL_USER | db_ddladmin |
| [email protected] | EXTERNAL_USER | db_backupoperator |
| [email protected] | EXTERNAL_USER | db_accessadmin |
| [email protected] | EXTERNAL_USER | db_securityadmin |
| [email protected] | EXTERNAL_USER | db_owner |
| dbo | SQL_User | db_owner |
If i try to add the user CloudS******** to DatabaseSend or DatabaseRecieve with CREATE USER i get the following error
The login already has an account under a different user name.
If i try to add the role dbmanager or loginmanager to [email protected] in DatabaseSend or DatabaseRecieve i get
Cannot alter the role 'dbmanger', because it does not exist or you do not have permission.
And if i try to add [email protected] to the role db_owner in Master i get the following error
Cannot alter the role 'db_owner', because it does not exist or you do not have permission.
Could some kind person please help this SQL Admin noob out? I think its because its not a uniform role across the databases, however i am unsure.
