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