PROBLEM: Sal Young is a member of your DBA team and today is his last day at work. You need to find out if his login is mapped to any database throughout your MS SQL environment.
SOLUTION: I used Central Management Server, PowerShell, and SQLPSX. I queried my Central Management Server to get the name of all SQL servers in my environment to then proceed and loop through my list of servers and databases to look for the pattern of the user name or login, in this case “syoung”.
Notice on line 2 how the value assigned to the variable is surrounded by asterisks. This will support the wildcard search on line 18 for any database account with extra characters before or after the string value.
On line 5 and 6 is the query executed against my Central Management Server to get the list of registered SQL Servers in my environment.
CLEAR
[string] $sDBUser = "*young*"
$sSQL = @"
SELECT DISTINCT [server_name]
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
WHERE [server_name] NOT LIKE '%.%'
"@
$aSQLServers = Invoke-Sqlcmd -ServerInstance "TUIRA" `
-Database "msdb" `
-Query $sSQL
foreach ($sSQLServer in $aSQLServers) {
$sSQLServer = $sSQLServer.server_name
Get-SqlDatabase -sqlserver "$sSQLServer"| `
% {Get-SqlUser $_}| `
WHERE {$_.Name -like "$sDBUser"}| `
SELECT Server, dbname, name
}