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