Grant access to Azure SQL PaaS

Grant access to Azure SQL PaaS

When you need to grant access to Azure SQL PaaS database, you can use following T-SQL scripts.

 Login to Azure database and run query against it. This will create user in database (user is from Azure Active Directory) and grant him datareader role. 

CREATE USER [user1\@domain.onmicrosoft.com] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember db_datareader, [user1\@domain.onmicrosoft.com];

 

If you don't need the Azure AD, you can create an SQL-user

CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a'; --doing this on Master DB
CREATE USER readonlyuser FROM LOGIN readonlylogin; --doing this on target DB
EXEC sp_addrolemember db_datareader, [readonlyuser]; --doing this on target DB

 

Check granted permissions

SELECT prm.permission_name
   , prm.class_desc
   , prm.state_desc
   , p2.name as 'Database role'
   , p3.name as 'Additional database role' 
FROM sys.database_principals AS p
JOIN sys.database_permissions AS prm
   ON p.principal_id = prm.grantee_principal_id
   LEFT JOIN sys.database_principals AS p2
   ON prm.major_id = p2.principal_id
   LEFT JOIN sys.database_role_members r
   ON p.principal_id = r.member_principal_id
   LEFT JOIN sys.database_principals AS p3
   ON r.role_principal_id = p3.principal_id
WHERE p.name = 'user1\@domain.onmicrosoft.com';

 

Also you can check all users from database and some details

SELECT * FROM sys.database_principals

 

Run some query as new user

EXECUTE AS USER = 'user1';  
SELECT * FROM [SalesLT].[ProductCategory];

 

 

Tags: ms sql server (en), azure (en)

PrintEmail

Add comment


Security code
Refresh