#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=, 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

About the author: Hemanth C Damecharla

Hemanth is a SQL*Server DBA and a PowerShell enthusiast who loves to keep looking for new things to tinker around with. He maintains that he can write poetry. He blogs at http://sqlchow.wordpress.com and you can follow him on Twitter: @sqlchow

Related Posts