SQL SERVER PS. Retreive Databases and Database Roles from instances

• KSQLDBA

  Ten Centuries

  Points: 1062

  More actions

  January 30, 2017 at 9:23 pm

  #318527

  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

  Mr SQLDBA

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  January 31, 2017 at 12:18 am

  #1925772

  For what purpose?

  Could you comment the code saying what you were trying to achieve, perhaps highlighting what works and what doesn't?

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• T Michael Herring

  SSC Eights!

  Points: 837

  More actions

  January 31, 2017 at 12:34 pm

  #1925939

  This should work for you, but I agree on the purpose question... My personal preference is to avoid use of SQLPS at most costs as it can be a bit of a performance pain. This script block uses the one SMO required for what you are wanting to accomplish, and nothing more.

  Clear-Host;
  # Change the SMO version to match whatever you have installed on the environment this script is executed from
  Add-Type -AssemblyName ("Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") -ErrorAction Stop;
  # instance names to process
  $instances = @("LOCALHOST\SQL1");
  $aggregate = @();
  foreach ($instance in $instances) {
    #connect to the instance with SMO
    $svr = New-Object Microsoft.SqlServer.Management.Smo.Server($instance);
    foreach ($db in $svr.Databases) {
      foreach ($role in $db.Roles) {
      $aggregate += New-Object PSObject -Property @{
        instance = $svr.Name;
        database_name = $db.Name;
        role_name = $role.Name;
      } | Select-Object instance, database_name, role_name;
      }
    } 
    # disconnect from the instance
    $svr.ConnectionContext.Disconnect();
  }
  $aggregate;
  # if you are only processing a single instance and you don't require the instance name
  # to be displayed in the output, you can use this instead of the above line...
  #$aggregate | Select-Object database_name, role_name;

  T. Michael Herring
  Database Administrator