Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

INTRODUCTION:

In this section, we will learn how to create login and users in Azure Synapse data warehouse.

Azure Synapse Analytics is an unlimited information analysis service aimed at large companies that was presented as the evolution of Azure SQL Data Warehouse (SQL DW), bringing together business data storage and macro or Big Data analysis.

Synapse provides a single service for all workloads when processing, managing and serving data for immediate business intelligence and data prediction needs. The latter is made possible by its integration with Power BI and Azure Machine Learning, due to Synapse’s ability to integrate mathematical machine learning models using the ONNX format. It provides the freedom to handle and query huge amounts of information either on demand serverless (a type of deployment that automatically scales power on demand when large amounts of data are available) for data exploration and ad hoc analysis, or with provisioned resources, at scale.

Step1:-

Note: Use [database] is not allow\supported in Azure Synapse.

Below is the code to create SQL authenticated login and user

Select Master database from object explore in SSMS and run below command to create login
CREATE login [LoginName] WITH password=’Complex_password’
GO

Step 2:

In Master database or whatever DB Pool where you intend to connect, select that in object explorer and in new query window run below..
now, need to create user to access Synapse server else we will get error.
create user UserName from login [LoginName]

Go

Note: If you do not create any user either in Master or any DB pool, you cant access the Azure Synapse server.

Hence you need to create User after login creation to access respective DB pool or Master DB.

Step3:-

Till here, user will be able to connect Azure synapse server but can not see any tables\view because user has not yet assigned with any roles.

exec sp_addrolemember ‘db_datareader’,’UserName’

Step 4 :

–Revoke role for user

exec sp_droprolemember ‘db_datareader’,’UserName’

Below is the code to create Contained user for Azure active directory user

CREATE USER [name@domain.com] 
FROM EXTERNAL PROVIDER 
WITH DEFAULT_SCHEMA = dbo;  
  
-- add user to role(s) in db 
exec sp_addrolemember 'db_datareader','name@domain.com'