As a Powershell newbie, i wanted to know if i can create a powershell app
that will search my list of SQL Server Instances (from a sql table) query
each one for the list of administrators of that server and then put that data
into a SQL table.



I finally found this function from Ron Dameron
(http://www.simple-talk.com/content/a...icle=576#forum) which
lists administrators of a server.

Then, i found RIchard Siddaway's example of how to use powershell to push
into a table.
http://richardsiddaway.spaces.live.c...entry?0aaed4f8 Now, it seems I'm not too far from solution!

Can anyone put this together for me to put the output of the function run
against a list of servers into another SQL Table?

If I had a solution to this, it will be my template for gathering all kinds
of useful info!
Thanks in advance!
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$sqlServer = ".\SQLEXPRESS"
$dbName = "TestDB"
$tblAdmins = "AdminRoleMembers"


# table to collect the information
# create a table named $tblAdmins on $dbName (if the table doesn't exist)
# with two columns, Server and Member

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlServer
$db = $srv.databases[$dbName]

if(!$db.tables[$tblAdmins]){

$tbl = new-object Microsoft.SqlServer.Management.Smo.Table ($db,$tblAdmins)
$col1=new-object Microsoft.SqlServer.Management.Smo.Column ($tbl,"Server",[Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(50))
$col2=new-object Microsoft.SqlServer.Management.Smo.Column ($tbl,"Member",[Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(50))

$col1.nullable = $false
$col2.nullable = $false

$tbl.columns.add($col1)
$tbl.columns.add($col2)
$tbl.create()

} else {
write-warning "skipping...table already exists"
}



## open database connection
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;
Initial Catalog=$dbName; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()


# list the sysadmin role members foreach server name in servers.txt
# and write the members to the $tblAdmins table


get-content servers.txt | foreach {
$sqlName = $_
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlName

$srv.roles["sysadmin"].enumServerRoleMembers() | foreach {
$cmd.CommandText = "INSERT INTO $tblAdmins VALUES ('$sqlName','$_')"
$null = $cmd.ExecuteNonQuery()
}
}


$conn.Close()





---
Shay Levy
Windows PowerShell MVP
http://blogs.microsoft.co.il/blogs/ScriptFanatic
PowerShell Toolbar: http://tinyurl.com/PSToolbar



BS> As a Powershell newbie, i wanted to know if i can create a
BS> powershell app that will search my list of SQL Server Instances
BS> (from a sql table) query each one for the list of administrators of
BS> that server and then put that data into a SQL table.
BS>
BS> I finally found this function from Ron Dameron
BS> (http://www.simple-talk.com/content/a...icle=576#forum)
BS> which lists administrators of a server.
BS>
BS> Then, i found RIchard Siddaway's example of how to use powershell to
BS> push into a table.
BS> http://richardsiddaway.spaces.live.c...6A74CF3E96!382
BS> .entry?0aaed4f8 Now, it seems I'm not too far from solution!
BS>
BS> Can anyone put this together for me to put the output of the
BS> function run against a list of servers into another SQL Table?
BS>
BS> If I had a solution to this, it will be my template for gathering
BS> all kinds
BS> of useful info!
BS> Thanks in advance!