Creating a SQL Data Warehouse user with a specific resource class

  • Article

In your data warehouse, you probably have multiple user groupings that perform different tasks: data loading; system tasks; querying; transformation and aggregations. Each of these groups has it’s own characteristics that can impact each other – a large data load might need a lot of memory while a user running a dashboard might not. Azure SQL Data Warehouse gives you the ability to manage resource utilization via resource classes. In this post, we’re going to add a login and user then adjust the resource class they are in. The steps are:

  1. Create a server level login
  2. Create a database level user
  3. Add the user to the resource class
  4. [Optional] Grant permissions

Create a Server Level LOGIN

The first step is to create a LOGIN at the server level (see CREATE LOGIN). We will connect to the master database on the logical server and create the LOGIN. In this case, I’m using a SQL login (username/password combination) managed by the SQL Server/SQL Data Warehouse services. You can also use Azure Active Directory Connect and Federation to utilize single sign-on from your corporate accounts. See the Adding AD Users and security Groups to Azure SQL Data Warehouse post to learn more.

 CREATE LOGIN cloudsaxl WITH PASSWORD = ‘<use a strong password>’;

Create a Database Level USER

The next step is to create a USER at the database level (see CREATE USER). We will connect to your SQL Data Warehouse database to execute the statement.

 CREATE USER cloudsaxl FOR LOGIN cloudsaxl;

Add the User to the XLargeRC resource class

The third step is to add the user to the resource class (see Concurrency and Resource Classes). We will connect to your SQL Data Warehouse database to execute the statement.

 EXEC sp_addrolemember 'xlargerc', 'cloudsaxl';

[Optional] Grant permissions

Now that the user is available on the server, you can optionally grant permissions to the user based on your workload needs. This could be granting a login for data loading into your data loading group that has read/write permissions or it could be adding a login for a user to have read only access. In the example below, I’m connected to my SQL Data Warehouse instance and am granting my user full permissions to the db_owner role.

 EXEC sp_addrolemember 'db_owner', 'cloudsaxl';