question

MrSQLDBA avatar image
MrSQLDBA asked

PS.SQL SERVER Will like to Retreive Databases and Database Roles from instances

I will like to Retrieve Databases and Database Roles from many instances in the domain with a PS script. have someone already done something similar? Do you have a script? can you do it for one instance and then many? I was able to get this far.The select work, return Names of Databases but no roles. need to Roles to work. This is only on localhost need to be remote to all instances too. any help ? _______ #import SQL Server module Import-Module SQLPS -DisableNameChecking [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL1" $dbs=$s.Databases #foreach-Object #{ } $database = $_ $dbRoles = New-Object ('Microsoft.SqlServer.Management.Smo.Databaserole') -ArgumentList $database, $role $dbRoles.Create() $dbs |Select Name, role # the select work, return Names of Databases but no roles. need to Roles to work. ____________________________________________________________________________ Thanks Mrdba
sql-server-2008sql-server-2012scriptpowershell
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Shawn_Melton avatar image
Shawn_Melton answered
You can use [dbatools module][1] to help with the SMO code. We have some commands being added around database roles but they are not in the current release. Import-Module dbatools $servers = 'manatarms\number12','manatarms' foreach ($s in $servers) { $srv = Connect-DbaSqlServer -SqlServer $s -ClientName 'DBA Just Making Job Easier' $srv.Databases.Roles | Select-Object @{Label="Server";Expression={$s}}, @{Label="Database";Expression={$_.Parent}}, @{Label="DatabaseRole";Expression={$_.Role}} } ![alt text][2] [1]: http://dbatools.io [2]: /storage/temp/3928-capture.png

capture.png (119.6 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.