functions/Find-DbaUserObject.ps1

Function Find-DbaUserObject
{
<#
.SYNOPSIS
Searches SQL Server to find user-owned objects (ie. not dbo or sa) or for any object owned by a specific user specified by the Pattern parameter.
 
.DESCRIPTION
Looks at the below list of objects to see if they are either owned by a user or a specific user (using the parameter -Pattern)
    Database Owner
    Agent Job Owner
    Used in Credential
    USed in Proxy
    SQL Agent Steps using a Proxy
    Endpoints
    Server Roles
    Database Schemas
    Database Roles
    Database Assembles
    Database Synonyms
 
 
.PARAMETER SqlInstance
SqlInstance name or SMO object representing the SQL Server to connect to. This can be a collection and receive pipeline input
 
.PARAMETER SqlCredential
PSCredential object to connect as. If not specified, current Windows login will be used.
 
.PARAMETER Pattern
The regex pattern that the command will search for
 
.NOTES
Author: Stephen Bennett, https://sqlnotesfromtheunderground.wordpress.com/
dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com)
Copyright (C) 2016 Chrissy LeMaire
License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
.LINK
https://dbatools.io/Find-DbaUserObject
 
.EXAMPLE
Find-DbaUserObject -SqlInstance DEV01 -Pattern ad\stephen
 
Searches user objects for owner ad\stephen
     
.EXAMPLE
Find-DbaUserObject -SqlInstance DEV01 -Verbose
 
Shows all user owned (non-sa, non-dbo) objects and verbose output
#>

    [CmdletBinding()]
    Param (
        [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $True)]
        [Alias("ServerInstance", "SqlServer", "SqlInstances")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [string]$Pattern
    )
    begin
    {
        if ($Pattern -match '^[\w\d\.-]+\\[\w\d\.-]+$')
        {
                Write-Verbose "Too few slashes, adding extra as required by regex"
                $Pattern = $Pattern.Replace('\', '\\')
        }
    }
    process
    {
        foreach ($Instance in $SqlInstance)
        {
            try
            {
                Write-Verbose "Connecting to $Instance"
                $server = Connect-SqlInstance -SqlInstance $Instance -SqlCredential $sqlcredential
            }
            catch
            {
                Write-Warning "Failed to connect to: $Instance"
                continue
            }
            
            $saname = Get-SaLoginName $server
            
            ## Credentials
            if (-not $pattern)
            {
                Write-Verbose "Gather data on credentials"
                $creds = $server.Credentials
                Write-Verbose "Gather data on proxy accounts"
                $proxies = $server.JobServer.ProxyAccounts
                Write-Verbose "Gather data on endpoints"
                $endPoints = $server.Endpoints | Where-Object { $_.Owner -ne $saname }
                Write-Verbose "Gather data on Agent Jobs ownership"
                $jobs = $server.JobServer.Jobs | Where-Object { $_.OwnerLoginName -ne $saname }
            }
            else
            {
                Write-Verbose "Gather data on credentials"
                $creds = $server.Credentials | Where-Object { $_.Identity -match $pattern }
                Write-Verbose "Gather data on proxy accounts"
                $proxies = $server.JobServer.ProxyAccounts | Where-Object { $_.CredentialIdentity -match $pattern }
                Write-Verbose "Gather data on endpoints"
                $endPoints = $server.Endpoints | Where-Object { $_.Owner -match $pattern }
                Write-Verbose "Gather data on Agent Jobs ownership"
                $jobs = $server.JobServer.Jobs | Where-Object { $_.OwnerLoginName -match $pattern }
            }
            
            
            ## dbs
            if (-not $pattern)
            {
                foreach ($db in $server.Databases | Where-Object{ $_.Owner -ne $saname })
                {
                    Write-Verbose "checking if $db is owned "
                    
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Database"
                        Owner = $db.Owner
                        Name = $db.Name
                        Parent = $db.Parent.Name
                    }
                }
            }
            else
            {
                foreach ($db in $server.Databases | Where-Object { $_.Owner -match $pattern })
                {
                    
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Database"
                        Owner = $db.Owner
                        Name = $db.Name
                        Parent = $db.Parent.Name
                    }
                }
            }
            
            ## agent jobs
            if (-not $pattern)
            {
                foreach ($job in $server.JobServer.Jobs | Where-Object { $_.OwnerLoginName -ne $saname })
                {
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Agent Job"
                        Owner = $job.OwnerLoginName
                        Name = $job.Name
                        Parent = $job.Parent.Name
                    }
                }
            }
            else
            {
                foreach ($job in $server.JobServer.Jobs | Where-Object { $_.OwnerLoginName -match $pattern })
                {
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Agent Job"
                        Owner = $job.OwnerLoginName
                        Name = $job.Name
                        Parent = $job.Parent.Name
                    }
                }
            }
            
            ## credentials
            foreach ($cred in $creds)
            {
                ## list credentials using the account
                
                [PSCustomObject]@{
                    ComputerName = $server.NetName
                    SqlInstance = $server.ServiceName
                    Type = "Credential"
                    Owner = $cred.Identity
                    Name = $cred.Name
                    Parent = $cred.Parent.Name
                }
            }
            
            ## proxies
            foreach ($proxy in $proxies)
            {
                [PSCustomObject]@{
                    ComputerName = $server.NetName
                    SqlInstance = $server.ServiceName
                    Type = "Proxy"
                    Owner = $proxy.CredentialIdentity
                    Name = $proxy.Name
                    Parent = $proxy.Parent.Name
                }
                
                ## list agent jobs steps using proxy
                foreach ($job in $server.JobServer.Jobs)
                {
                    foreach ($step in $job.JobSteps | Where-Object { $_.ProxyName -eq $proxy.Name })
                    {
                        [PSCustomObject]@{
                            ComputerName = $server.NetName
                            SqlInstance = $server.ServiceName
                            Type = "Agent Step"
                            Owner = $step.ProxyName
                            Name = $step.Name
                            Parent = $step.Parent.Name #$step.Name
                        }
                    }
                }
            }
            
            
            ## endpoints
            foreach ($endPoint in $endPoints)
            {
                [PSCustomObject]@{
                    ComputerName = $server.NetName
                    SqlInstance = $server.ServiceName
                    Type = "Endpoint"
                    Owner = $endpoint.Owner
                    Name = $endPoint.Name
                    Parent = $endPoint.Parent.Name
                }
            }

            ## Server Roles
            if (-not $pattern)
            {
                foreach ($role in $server.Roles | Where-Object{ $_.Owner -ne $saname })
                {
                    Write-Verbose "checking if $db is owned "
                    
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Server Role"
                        Owner = $role.Owner
                        Name = $role.Name
                        Parent = $role.Parent.Name
                    }
                }
            }
            else
            {
                foreach ($role in $server.Roles | Where-Object { $_.Owner -match $pattern })
                {
                    
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Server Role"
                        Owner = $role.Owner
                        Name = $role.Name
                        Parent = $role.Parent.Name
                    }
                }
            }

            
            ## Loop internal database
            foreach ($db in $server.Databases | Where-Object IsAccessible)
            {
                Write-Verbose "Gather user owned object in database: $db"
                ##schemas
                $sysSchemas = "DatabaseMailUserRole", "db_ssisadmin", "db_ssisltduser", "db_ssisoperator", "SQLAgentOperatorRole", "SQLAgentReaderRole", "SQLAgentUserRole", "TargetServersRole", "RSExecRole"
                
                if (-not $pattern)
                {
                    $schemas = $db.Schemas | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -ne "dbo" -and $sysSchemas -notcontains $_.Owner }
                }
                else
                {
                    $schemas = $db.Schemas | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -match $pattern -and $sysSchemas -notcontains $_.Owner }
                }
                foreach ($schema in $schemas)
                {
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Schema"
                        Owner = $schema.Owner
                        Name = $schema.Name
                        Parent = $schema.Parent.Name
                    }
                }
                
                ## database roles
                if (-not $pattern)
                {
                    $roles = $db.Roles | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -ne "dbo" }
                }
                else
                {
                    $roles = $db.Roles | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -match $pattern }
                }
                foreach ($role in $roles)
                {
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Database Role"
                        Owner = $role.Owner
                        Name = $role.Name
                        Parent = $role.Parent.Name
                    }
                }
                
                ## assembly
                if (-not $pattern)
                {
                    $assemblies = $db.Assemblies | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -ne "dbo" }
                }
                else
                {
                    $assemblies = $db.Assemblies | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -match $pattern }
                }
                
                foreach ($assembly in $assemblies)
                {
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Database Assembly"
                        Owner = $assembly.Owner
                        Name = $assembly.Name
                        Parent = $assembly.Parent.Name
                    }
                }
                
                ## synonyms
                if (-not $pattern)
                {
                    $synonyms = $db.Synonyms | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -ne "dbo" }
                }
                else
                {
                    $synonyms = $db.Synonyms | Where-Object { $_.IsSystemObject -eq 0 -and $_.Owner -match $pattern }
                }
                
                foreach ($synonym in $synonyms)
                {
                    [PSCustomObject]@{
                        ComputerName = $server.NetName
                        SqlInstance = $server.ServiceName
                        Type = "Database Synonyms"
                        Owner = $synonym.Owner
                        Name = $synonym.Name
                        Parent = $synonym.Parent.Name
                    }
                }
            }
        }
    }
}