#PSTip Finding all databases to which a login is mapped

Note: This tip requires PowerShell 2.0 or above.

One of the projects on which I work requires a validation step after each deployment. We need to validate if the logins have been mapped to all required roles in each of the application databases via users created for the login.

In todays tip, we will see how we can find all the databases to which a login is mapped. The login object in SMO has a method called EnumDatabaseMappings() which enumerates the login account mappings to databases and database users. We will use the Test-SQLLogin function described in an earlier tip.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$login = 'TestUser'


if((Test-SQLLogin -SqlLogin $login))
{
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
    $server.Logins["$login"].EnumDatabaseMappings()| Select DBName, UserName
}

DBName UserName
------ --------
master TestUser
msdb TestUser