How to add a user to a SQL server role with powershell?

Question

I need a working example on how to assign a fixed server role to a user in Azure SQL database. It seems I can't even access Roles collection:

$DBServerBulkRole = $DBServer.Roles | where {$_.Name -eq 'bulkadmin'};
$DBServerBulkRole.AddMember($DBLoginName);

This generates

The following exception occurred while trying to enumerate the collection: "Operation not supported on version 12.0.600 SqlAzureDatabase."

Answer 1

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql

This states: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead."

I recommend you basically rewrite a function in powershell to perform the same call but utlizing the alter role command. This should resolve your problem. Azure's implementation of SQL is slightly different and there are certain commands that are not available. Also, as seen in the documentation I believe this is now a deprecated procedure.

-- Syntax for SQL Server (starting with 2012) and Azure SQL Database  

ALTER ROLE  role_name  
{  
       ADD MEMBER database_principal  
    |  DROP MEMBER database_principal  
    |  WITH NAME = new_name  
}  
[;]  

From: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql

Hope this helps!