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."

Kowalchick · Accepted Answer · 2017-06-22 17:12:45Z

2

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!

answered Jun 22, 2017 at 17:12

Kowalchick

4483 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kowalchick Over a year ago

For guidance on creating the function utilizing this syntax I recommend researching the invoke-sqlcmd: learn.microsoft.com/en-us/sql/powershell/invoke-sqlcmd-cmdlet

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related