search

dsccommunity / SqlServerDsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.
MIT License
360 stars 227 forks source link

SqlDatabaseRole: Add support for nested roles #1766

Closed borgesarnaldo closed 2 years ago

borgesarnaldo commented 2 years ago

Problem description

SQL Server supports both users and user-defined database roles being members of a database role. The SqlDatabaseRole resource should be enhanced to add this support. Currently, the resource checks whether all Members, MembersToInclude, or MembersToExclude are valid Users on the database.

Verbose logs

N/A

DSC configuration

SqlDatabaseRole 'Role-DatabaseMailUserRole'
{
    ServerName = 'sqltest.company.local'
    InstanceName = 'DSC'
    DatabaseName = 'msdb'
    Name = 'DatabaseMailUserRole'
    MembersToInclude = 'public'
    Ensure = 'Present'
    PsDscRunAsCredential = $SqlAdministratorCredential
}

Suggested solution

Instead of solely checking for a valid database User, the resource should also validate whether it matches a Role defined in the database.

SQL Server edition and version

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

SQLPS
SqlServer
SqlServerDsc

Operating system

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

PowerShell version

PSVersion                      5.1.17763.2931
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.2931
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

SqlServerDsc 15.2.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.2.0\SqlServerDsc.psd1
borgesarnaldo commented 2 years ago

Sorry for this issue. According to Microsoft, the special principal 'public' cannot be used in this case.