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 Dabtabase Assembles Database Synonyms .PARAMETER SqlInstance SqlInstance name or SMO object representing the SQL Server to connect to. This can be a collection and recieve 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 Original Author: Stephen Bennett, https://sqlnotesfromtheunderground.wordpress.com/ dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. .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")] [object[]]$SqlInstance, [System.Management.Automation.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-SqlServer -SqlServer $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 } } ## proxys 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 { $_.Status -eq "Normal" }) { 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 } } } } } } |