Mapping a login to a user in Azure SQL Data Warehouse

  • Article

Recently I got an inquiry from one customer who needed to move Azure SQL Data Warehouse(ADW) database into another existing Azure SQL Server. We can move Azure SQL Server including ADW database with "Move" function in Azure Portal.  Customer had to move only ADW database. After moving ADW database through creating new database from backup successfully, customer received the following message when connecting to new ADW database in new SQL Server with a login ("ETLLogin").

This message is a result of incorrect mapping a login ("ETLLogin") in master database to a user ("ETLUser")  in user database on new SQL Server. This error can be simply resolved by ALTER USER command to update SID of a user ("ETLUser") in user database.

From old SQL Server,

1) Connect to master database and check SID of a login ("ETLLogin") in master database.

select name, sid, type_desc from sys.sql_logins where name = 'ETLLogin'

ETLLogin 0x010600000000006400000000000000005BE84C3C0FD46B4399F03F785DE81F32 SQL_Login

2) Connect to user database and check a user that has same SID value at first step. In this example, the user ("ETLUser")  has same SID value with the login ("ETLLogin").

SELECT name, sid, type_desc, authentication_type_desc FROM sys.database_principals where sid = 0x010600000000006400000000000000005BE84C3C0FD46B4399F03F785DE81F32

ETLUser 0x010600000000006400000000000000005BE84C3C0FD46B4399F03F785DE81F32 SQL_USER INSTANCE

From new SQL Server,

3) Connect to master database and check SID of the login ("ETLLogin") in master database, If it doesn't exist, create new login and note SID value.

select name, sid, type_desc from sys.sql_logins where name = 'ETLLogin'

ETLLogin 0x01060000000000640000000000000000A8E9A04A1C58C14EAF2FDA894DEF7C66 SQL_Login

4) Connect to user database and check SID of the user ("ETLUser") in user database. The SID of user ("ETLUser") is not equal to that of the login ("ETLLogin").

SELECT name, sid, type_desc, authentication_type_desc FROM sys.database_principals where name = 'ETLUser'

ETLUser 0x010600000000006400000000000000005BE84C3C0FD46B4399F03F785DE81F32 SQL_USER INSTANCE

5) Connect to user database and run ALTER USER command to match up SID value between the login ("ETLLogin") and the user ("ETLUser").

ALTER USER ETLUser WITH LOGIN = ETLLogin

6) Connect to user database and check SID of the user ("ETLUser") in user database. It is updated and matched up with SID of the login ("ETLLogin").

SELECT name, sid, type_desc, authentication_type_desc FROM sys.database_principals where name = 'ETLUser'

ETLUser 0x01060000000000640000000000000000A8E9A04A1C58C14EAF2FDA894DEF7C66 SQL_USER INSTANCE

7) Now the login ("ETLLogin") can connect to ADW database successfully.