SqlRole: User 'sa' is not allowed to be dropped from role sysadmin

[Chiliyago]

Details of the scenario you tried and the problem that is occurring:
I have the following 2 DSC Resource statements one works and the other does not. The first successfully adds the account to the securityadmin SQL role and the second fails to add the same account to the sysadmin SQL role.

`
The DSC configuration that is using the resource (as detailed as possible):

xSQLServerRole Set_securityadmin_Roles4_FarmSetup{

    Members = @($FarmSetupCredential.UserName)
    SQLServer = $SqlServerName
    SQLInstanceName = $SqlInstanceName
    ServerRoleName = "securityadmin"
    Ensure = "Present"
}
xSQLServerRole Set_sysadmin_Roles4_FarmSetup{

    Members = @($FarmSetupCredential.UserName)
    SQLServer = $SqlServerName
    SQLInstanceName = $SqlInstanceName
    ServerRoleName = "sysadmin"
    Ensure = "Present"
}

Error Message for Set_sysadmin_Roles4_FarmSetup

PowerShell DSC resource MSFT_xSQLServerRole  failed to execute Set-TargetResource functionality with error message: Failed to drop member sa to the 
server role named sysadmin on SP-SQL1\MSSQLSERVER. InnerException: Exception calling "DropMember" with "1" argument(s): "Drop member failed for 
ServerRole 'sysadmin'. " 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : SP-SQL1

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
I am using SQL 2016 on Windows Server 2012 R2

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:

xSQLServerRole v 7.0.0.0

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
SQLServer 7.0.0.0

[johlju]

This is by design by SQL Server (it seems). When using parameter Members it will replace all members with those that is assigned to Members. For sysadmin role this does not work since SQL Server does not allow user 'sa' to be dropped.
Use parameter MembersToInclude to add the members you want, and still keep those already present in the role.

We could add a note to the resource description that this is the case.

[Fiander]

Working on this one.