sql salt

T-SQL Tuesday #39

This week’s T-SQL Tuesday is being hosted by Wayne Sheffield ( blog | twitter ), and the topic is to blog about anything PowerShell and SQL Server related.  As a SQL Server professional, I use PowerShell day-in-day-out and I love every second of it.  Today I am going to write about how to use SMO (via PowerShell) to get both server and database role members for a list of SQL Server instances.

When would this come in handy?  Say you just found out the implications of membership in the securityadmin fixed server role.  Wide-eyed, your brain starts racing on how you can reach across all 1,000 database servers in your environment to get a list of all members in the securityadmin fixed server role.  There are a couple of ways to do this, and my tool of choice for this type of requirement would be PowerShell.  Note: the below code is meant to show one way to accomplish this task, and it is not to be copy-and-pasted in production without thorough testing.

There are a total of four cmdlets that I utilize for this purpose.  They are as follows:

Get-SqlServerRoleMembers – gets a list of members for a particular server role (or roles)

Get-SqlServerDbRoleMembers – gets a list of members for a particular database role (or roles)

Is-SqlServerRole – takes a Server object and role name as parameters and outputs a Boolean value whether or not the principal is a role (this is consumed by Get-SqlServerRoleMembers)

Is-SqlServerDbRole – takes a Database object and role name as parameters and outputs a Boolean value whether or not the principal is a role (this is consumed by Get-SqlServerDbRoleMembers)

Now getting to the code.  The last two cmdlets (Is-SqlServerRole and Is-SqlServerDbRole) are really just worker functions for the two main cmdlets and don’t require direct calls.  All they really do is find out whether a member is a role.  The reason I had to write these is because the SMO objects didn’t readily provide this information, and with a huge dump of data it would be easier if that was provided.  Not to mention you want to find out if a login has the nested membership, so these cmdlets allowed me to get recursive with the Get-SqlServer[Db]RoleMembers cmdlets.

Is-SqlServerRole

Function Is-SqlServerRole {
    param (
        [Parameter(Mandatory = $true, Position = 0, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
        [Microsoft.SqlServer.Management.Smo.Server] $SqlServerInstance,

        [Parameter(Mandatory = $true, Position = 1)]
        [String] $RoleName
    )

    $SqlCommand = "
        declare @principal_name sysname;
        set @principal_name = '$RoleName';

        select 1
        from sys.server_principals
        where type = 'R'
        and name = @principal_name"

    try {
        if ($SqlServerInstance.Status -eq [Microsoft.SqlServer.Management.Smo.ServerStatus]::Online) {
            if ($SqlServerInstance.Databases["master"].ExecuteWithResults($SqlCommand).Tables[0].Rows.Count -gt 0) {
                return $true
            }
            else {
                return $false
            }
        }
        else {
            return $false
        }
    }
    catch {
        Write-Error $_.Exception
    }
}

Is-SqlServerDbRole

Function Is-SqlServerDbRole {
    param (
        [Parameter(Mandatory = $true, Position = 0, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
        [Microsoft.SqlServer.Management.Smo.Database] $SqlDatabase,

        [Parameter(Mandatory = $true, Position = 1)]
        [String] $RoleName
    )

    $SqlCommand = "
        declare @principal_name sysname;
        set @principal_name = '$RoleName';

        select 1
        from sys.database_principals
        where type = 'R'
        and name = @principal_name;"

    try {
        if ($SqlDatabase.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal) {
            if ($SqlDatabase.ExecuteWithResults($SqlCommand).Tables[0].Rows.Count -gt 0) {
                return $true
            }
            else {
                return $false;
            }
        }
        else {
            return $false
        }
    }
    catch {
        Write-Error $_.Exception
    }
}

The real work is done with the following two cmdlets:  Get-SqlServerRoleMembers and Get-SqlServerDbRoleMembers.  Below are a couple of calls to these cmdlets, and you can see that they take a list of SQL Server instances and that is then piped to the aforementioned cmdlets.  By specifying the role list as a string array, we are able to pull the role (or roles) members that we are concerned with.  And, as always, if you need to use SQL Server authentication there is that capability.

Usage

"sqlbox1", "sqlbox2" |
    Get-SqlServerRoleMembers -SqlServerRoleList "sysadmin", "securityadmin" -SqlAuth -SqlCredential $Creds

"sqlbox1", "sqlbox2" |
    Get-SqlServerDbRoleMembers -SqlServerRoleList "db_owner"

Now getting into the code.  All we really do here is loop through the server roles in Get-SqlServerRoleMembers, and the database roles in Get-SqlServerDbRoleMembers.  Not rocket science, just using the ever-so-flexible and easy-to-utilize SMO namespace.

Get-SqlServerRoleMembers

Function Get-SqlServerRoleMembers {
    param (
        [Parameter(Mandatory = $false, Position = 0, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
        [String[]] $SqlInstanceNameList = $env:COMPUTERNAME,

        [Parameter(Mandatory = $true, Position = 1)]
        [String[]] $SqlServerRoleList,

        [Parameter(Mandatory = $false, Position = 2)]
        [Switch] $SqlAuth,

        [Parameter(Mandatory = $false, Position = 3)]
        [PSCredential] $SqlCredential
    )

    begin {
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
            Out-Null
    }

    process {
        try {
            foreach ($SqlInstanceName in $SqlInstanceNameList) {
                $SqlInstance = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlInstanceName)
                if ($SqlAuth) {
                    $SqlInstance.ConnectionContext.LoginSecure = $false
                    $SqlInstance.ConnectionContext.Login = $SqlCredential.UserName
                    $SqlInstance.ConnectionContext.SecurePassword = $SqlCredential.Password
                }

                $SqlInstance.Roles |
                    Where-Object {$_.Name -in $SqlServerRoleList} |
                    ForEach-Object {
                        foreach($RoleMember in $_.EnumMemberNames()) {
                            $RoleMemberObj = New-Object System.Object
                            $RoleMemberObj |
                                Add-Member -MemberType NoteProperty -Name "ServerName" -Value $SqlInstance.Name
                            $RoleMemberObj |
                                Add-Member -MemberType NoteProperty -Name "RoleName" -Value $_.Name
                            $RoleMemberObj |
                                Add-Member -MemberType NoteProperty -Name "MemberName" -Value $RoleMember

                            if (Is-SqlServerRole -SqlServerInstance $SqlInstance -RoleName $RoleMember) {
                                Get-SqlServerRoleMembers -SqlInstanceNameList $SqlInstanceName -SqlServerRoleList $RoleMember -SqlAuth:$SqlAuth -SqlCredential $SqlCredential
                                $RoleMemberObj |
                                    Add-Member -MemberType NoteProperty -Name "MemberType" -Value "Role"
                            }
                            else {
                                $RoleMemberObj |
                                    Add-Member -MemberType NoteProperty -Name "MemberType" -Value "Login"
                            }

                            Write-Output $RoleMemberObj
                        }
                    }
            }
        }
        catch {
            Write-Error $_.Exception
        }
    }
}

Get-SqlServerDbRoleMembers

Function Get-SqlServerDbRoleMembers {
    param (
        [Parameter(Mandatory = $false, Position = 0, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
        [String[]] $SqlInstanceNameList = $env:COMPUTERNAME,

        [Parameter(Mandatory = $true, Position = 1)]
        [String[]] $SqlServerRoleList,

        [Parameter(Mandatory = $false, Position = 2)]
        [Switch] $SqlAuth,

        [Parameter(Mandatory = $false, Position = 3)]
        [PSCredential] $SqlCredential
    )

    begin {
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
            Out-Null
    }

    process {
        try {
            foreach ($SqlInstanceName in $SqlInstanceNameList) {
                $SqlInstance = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlInstanceName)
                if ($SqlAuth) {
                    $SqlInstance.ConnectionContext.LoginSecure = $false
                    $SqlInstance.ConnectionContext.Login = $SqlCredential.UserName
                    $SqlInstance.ConnectionContext.SecurePassword = $SqlCredential.Password
                }

                foreach ($SqlDatabase in $SqlInstance.Databases) {
                    try {
                        $SqlDatabase.Roles |
                            Where-Object {$_.Name -in $SqlServerRoleList} |
                                ForEach-Object {
                                    foreach($DbRoleMember in $_.EnumMembers()) {
                                        $DbRoleMemberObj = New-Object System.Object
                                        $DbRoleMemberObj |
                                            Add-Member -MemberType NoteProperty -Name "ServerName" -Value $SqlInstance.Name
                                        $DbRoleMemberObj |
                                            Add-Member -MemberType NoteProperty -Name "DatabaseName" -Value $SqlDatabase.Name
                                        $DbRoleMemberObj |
                                            Add-Member -MemberType NoteProperty -Name "RoleName" -Value $_.Name
                                        $DbRoleMemberObj |
                                            Add-Member -MemberType NoteProperty -Name "MemberName" -Value $DbRoleMember

                                        if (Is-SqlServerDbRole -SqlDatabase $SqlDatabase -RoleName $DbRoleMember) {
                                            $DbRoleMemberObj |
                                                Add-Member -MemberType NoteProperty -Name "MemberType" -Value "Role"
                                        }
                                        else {
                                            $DbRoleMemberObj |
                                                Add-Member -MemberType NoteProperty -Name "MemberType" -Value "User"
                                        }

                                        Write-Output $DbRoleMemberObj
                                    }
                                }
                        }
                    catch {
                        Write-Error $_.Exception
                    }
                }
            }
        }
        catch {
            Write-Error $_.Exception
        }
    }
}

Voila!  This is a good starting point.  It shows just how powerful our beloved PowerShell can be, turning an impossible task into nothing with just a quick script.

If there are any comments, questions, or suggestions please feel free to leave a comment below or email me at sqlsalt@outlook.com.

Tags: PowerShell, security, t-sql tuesday