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. 

EXEC sp_addrolemember db_datareader, [user1\];


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
   , as 'Database role'
   , 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 = 'user1\';


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)


Add comment

Security code