#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}

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

%d bloggers like this: