SqlDatabasePermission: Cannot grant or revoke permissions from database role

[Haratsu]

Details of the scenario you tried and the problem that is occurring

As I use SqlServerDsc primarily in ansible playbooks and the goal there is to punish manual changes I tried to ensure permissions for predefined server roles like db_datawriter

This failed as SQL server could not find a login named "db_datawriter". The main problem seems to be that SQLDatabasePermisssions does not check for roles but only for logins.

Verbose logs showing the problem

  invocation:
    module_args:
      Database: **secret**
      DependsOn: null
      Ensure: Present
      InstanceName: **secret**
      Name: db_datawriter
      PermissionState: Grant
      Permissions:
      - Connect
      - Update
      - Delete
      - Execute
      - Insert
      PsDscRunAsCredential_password: null
      PsDscRunAsCredential_username: null
      ServerName: **secret**
      module_version: latest
      resource_name: SqlDatabasePermission
  module_version: 13.5.0
  msg: 'Failed to invoke DSC Test method: PowerShell DSC resource MSFT_SqlDatabasePermission  failed to execute Test-TargetResource functionality with error message: System.Exception: The login ''db_datawriter'' does not exist on the instance. '
  reboot_required: false
  verbose_test:
  - Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceTest,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
  - An LCM method call arrived from computer **secret** with user sid **secret**.
  - '[**secret**]: LCM:  [ Start  Test     ]  [[SqlDatabasePermission]DirectResourceAccess]'
  - '[**secret**]:                            [[SqlDatabasePermission]DirectResourceAccess] Determines if the user ''db_datawriter'' has the correct permissions in the database ''**secret**'' on the instance ''**secret**''.'
  - '[**secret**]:                            [[SqlDatabasePermission]DirectResourceAccess] Get permissions for the user ''db_datawriter'' in the database ''**secret**'' on the instance ''**secret**''.'
  - '[**secret**]:                            [[SqlDatabasePermission]DirectResourceAccess] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)'
  - '[**secret**]:                            [[SqlDatabasePermission]DirectResourceAccess] Connecting as current user ''NT AUTHORITY\SYSTEM'' using integrated security. (SQLCOMMON0054)'
  - '[**secret**]:                            [[SqlDatabasePermission]DirectResourceAccess] Connected to SQL instance ''**secret**''. (SQLCOMMON0018)'
  - '[ABBST-TEST]: LCM:  [ End    Test     ]  [[SqlDatabasePermission]DirectResourceAccess] False in 0.0780 seconds.'
  - Operation 'Invoke CimMethod' complete.
  - Time taken for configuration job to complete is 0.223 seconds

Suggested solution to the issue

Add Flag to DSc to either change role or user permissions

The DSC configuration that is used to reproduce the issue (as detailed as possible)

- name: Ensure database role 'db_datawriter' has all permissions
  win_dsc:
    resource_name: SqlDatabasePermission
    ServerName: "{{ ansible_host }}"
    InstanceName: "{{ mssql_instance_name }}"
    Database: "{{ db_name }}"
    Name: "db_datawriter"
    Permissions:
      - "Connect"
      - "Update"
      - "Delete"
      - "Execute"
      - "Insert"
    PermissionState: "Grant"
    Ensure: Present

SQL Server edition and version the target node is running

SQL Server 2017

SQL Server PowerShell modules present on the target node

Name  Version Path                                                                                     
----  ------- ----                                                                                     
SQLPS 14.0    D:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName               : Microsoft Windows Server 2019 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

Version and build of PowerShell the target node is running

Name                           Value                                                                                                                                     
----                           -----                                                                                                                                     
PSVersion                      5.1.17763.1007                                                                                                                            
PSEdition                      Desktop                                                                                                                                   
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                   
BuildVersion                   10.0.17763.1007                                                                                                                           
CLRVersion                     4.0.30319.42000                                                                                                                           
WSManStackVersion              3.0                                                                                                                                       
PSRemotingProtocolVersion      2.3                                                                                                                                       
SerializationVersion           1.1.0.1   

Version of the DSC module that was used

Name         Version Path                                                                            
----         ------- ----                                                                            
SqlServerDsc 13.5.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.5.0\SqlServerDsc.psd1

[johlju]

Oh, that was a big bug. 😕 I like the suggested solution. Labeling it as help wanted and hope someone in the community can work on it.

[johlju]

Working on fixing this now. Although this will only work for user-defined roles like 'public'. Fixed roles like 'db_datareader' cannot be used as database principals.