#PSTip Finding all roles a particular login is mapped to in all databases

Note: This tip requires PowerShell 2.0 or above.

In a previous tip, we checked out how to get the names of all databases to which a login is mapped and the username for that login in each database. Once, we have this information, we can enumerate the roles that are assigned to the user mapped to our login using EnumRoles() method.

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' #SQLlogin or a windows login.

if((Test-SQLLogin -SqlLogin $login))
{
    $server.Logins["$login"].EnumDatabaseMappings() |
    Select DBName, UserName, @{Name="AssignedRoles"; Expression={@($server.Databases[$_.DBName].Users[$_.UserName].EnumRoles())}}
}

DBName UserName AssignedRoles
------ -------- -------------
master TestUser {db_datareader, db_datawriter}
msdb   TestUser {db_backupoperator, db_datareader, db_datawriter}