CODEWITHSUNDEEP
sql-serverazureazure-sql-databaseazure-active-directory
STORM
STORM

Reputation: 4331

How to add an AAD group with datawrite and dataread permissions to an Azure DB through PowerShell

I have an Azure SQL Server with an SQL database. I would like to add an AAD Group with datawrite and dataread permissions to this database through PowerShell.

I have no idea how to do this. Can anyone help?

Upvotes: 1

Views: 2586

Answers (3)

Sage
Sage

Reputation: 4937

Adding a user to a role is usually accomplished with SQL Statements. This is how this would be done with SQL. 

CREATE USER [group name]
FROM external provider
DEFAULT_SCHEMA dbo

Once the user has been added, you can then add them to a group by issuing the following statements;

ALTER ROLE db_datareader ADD MEMBER [group_name]
ALTER ROLE db_datawriter ADD MEMBER [group_name]

Note, within SSMS, you must be in the context of the database you want to add the user to. Azure SQL does not support USE statements, so ensure you selected the correct database.

To do it through powershell, you would probably want to use the following CmdLet, Add-RoleMember, but I have not used these CmdLets with Azure SQL Server before.

This is one way to add users to a SQL database via Powershell;

$Instance = $ENV:AzureSQLServer + ".database.windows.net"

$Query = "CREATE USER [$ENV:AdUser] FROM EXTERNAL PROVIDER"
$ConnString = "Server=" + $Instance + ";Database=master;Authentication=Active Directory Password;UID=" + $Env:SqlAdminUser + ";Password=" + $Env:SqlAdminPass + ";Trusted_Connection=false;Encrypt=True;Connection Timeout=30;"

Invoke-SQlCmd -ConnectionString $ConnString -Query $Query

We use this script in a PowerShell task in Jenkins to add users to databases. The Statements could be modified to also add the users to the appropriate roles as well.

Upvotes: 1

Sa Yang
Sa Yang

Reputation: 9401

I assume that you want to set AAD Group as AAD admin for your SQL database.

With this scenario, you can use Set-AzureRmSqlServerActiveDirectoryAdministrator:

Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DisplayName "DBAs" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353b"

Result:

ResourceGroupName ServerName DisplayName ObjectId 
----------------- ---------- ----------- -------- 
ResourceGroup01   Server01   DBAs        40b79501-b343-44ed-9ce7-da4c8cc7353b

NOTE:

You can only set the AAD Group with securtiy enabled.

Please let me know if this helps!

Upvotes: 1

Martin Brandl
Martin Brandl

Reputation: 58931

You first have to create an ADD group using e. g. the New-AzureADGroup cmdlet that is part of the AzureAD module.

Then you have to assign the desired role to your desired resource (e. g. the SQL DB Contributor role to your DB Server) using the New-AzureRmRoleAssignment cmdlet.

Further reading: Manage role-based access control with Azure PowerShell

Upvotes: 0

Related Questions