Which User belongs to which database role in SQL Server with Powershell

The below query get the information from listed servers and all the databases that are existing under that particular instance.
cls
Import-Module sqlps -DisableNameChecking
 $Query="SELECT @@servername as ServerName,DBName=DB_NAME(),DP1.name AS DatabaseRoleName,  
   isnull (DP2.name, 'No members') AS DatabaseUserName  
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R' and DP2.name='NameHere'
ORDER BY DP1.name"
$instanceNames=@("SERVER1","SERVER2","SERVER3","SERVER4")
$instanceNames|
ForEach-Object{
$IndInstance=$_
#[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")
$Server=New-object `
-TypeName "Microsoft.SQLserver.Management.Smo.Server" `
-ArgumentList $IndInstance
$IndDatabases=$Server.Databases
$IndDatabases|
ForEach-Object{
$SingleDb=$_.Name
$SingleDb|
ForEach-Object{
Invoke-Sqlcmd `
-ServerInstance $IndInstance `
-Query  $Query `
-Database $SingleDb
}
}
}

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server