Create a User to run ETL on Azure Synapse Analytics – Quick Tip

To run ETL on Azure Data Warehouse, it’s a good idea to create a user in a higher resource class.

There are a number of ways to authenticate to Azure DW. See – Controlling and granting database access to SQL Database and SQL Data Warehouse

The easiest (from a development perspective) is to create an SQL Server Login for the server in the master database, and then create a corresponding user in the database.

First, connect to the master database via Visual Studio SQL Server Object Explorer:

Next, run the following statement to create the SQL Server login in the master database:

CREATE LOGIN LoadUser WITH PASSWORD = '<strong password>';

Then connect to SQL Data Warehouse database that you want to add the user too and run:

CREATE USER LoadUser;

Then add the user to an appropriate role e.g.

EXEC sp_addrolemember 'db_owner', 'LoadUser';

and finally, add the user to a resource class:

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

 

[box type=”download”] If you found this useful please like (via one of the share buttons below) or link to it, to help others find it![/box]