#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
Filed in: Columns, Tips and Tricks Tags: , , ,
© 6725 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: