Blog History

January 6, 2021

Azure SQL permissions

Using Azure console to give IAM roles only gives them console administrative permissions, it does not give those users permissions inside of SQL. You must create these users and permissions manually [1].

 


 

 

 

 

 

To view the permissions given to the users:

SELECT DP1.name AS DatabaseRoleName,  
   isnull (DP2.name, 'No members') AS DatabaseUserName  
 FROM sys.database_role_members AS DRM
 RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
 LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

To create SQL logins:
#run on master
CREATE USER [user@domain.com] 
FROM EXTERNAL PROVIDER 
ALTER ROLE dbmanager ADD MEMBER [user@domain.com] 
ALTER ROLE loginmanager ADD MEMBER [user@domain.com] 

To give users database permissions:
#run on DB
CREATE USER [user@domain.com] 
FROM EXTERNAL PROVIDER 
ALTER ROLE db_datareader ADD MEMBER [user@domain.com] 
ALTER ROLE db_datawriter ADD MEMBER [user@domain.com] 
ALTER ROLE db_owner ADD MEMBER [user@domain.com] 

The users might not need db_owner, depending on what they are trying to do to the database.

References:

[1] https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases/

No comments:

Post a Comment