July 18, 2018 at 1:31 pm
I know there are multiple ways to skin the powershell cat and am currently settled on this solution.
In this example, I use 'Microsoft.SQLServer.Management.SMO.Server' to retrieve data (rather than a sql query) and write to a central database with a simple Invoke-Sqlcmd. I'm wondering if there's a simpler way to do this. I understand for specialized tasks, I'll need to create custom queries, procs, and views, but hoping this way of doing things (plus a few other modules I can import), will cover a vast majority of my administrative tasks.
Next steps will be error checking (try/catch/finally) and logging, which will be trivial. Also, I am ramping up System Center Orchestrator activities and simplicity will be key for my sanity I think (that is, if I remain a DBA). 🙂
Import-Module sqlserver -DisableNameChecking
#Do stuff, such as create $instances list, either from a file or control table in a DB.
# In our case, with central managed databases, we use msdb.dbo.sysmanagement_shared_registered_servers_internal
# and msdb.dbo.sysmanagement_shared_server_groups_internal groups
$mydata = New-Object System.Data.Table
$mydata.Columns.Add("InstanceName", "String") | Out-Null
$mydata.Columns.Add("Login", "String") | Out-Null
# This block of code populates a data table with all sysadmins on the target instance
foreach ($sqlinstance in $instances)
{
$server = New-Object 'Microsoft.SQLServer.Management.SMO.Server' $sqlinstance.InstanceName
foreach ($syslogin in ($srv.roles['sysadmin'].EnumServerRoleMembers()))
{
$newrow = $mydata.NewRow()
$newrow.InstanceName = $sqlinstance.InstanceName
$newrow.Login = $syslogin
$mydata.Rows.Add($newrow)
}
}
# This block of code excludes certain accounts we know are okay to be sysadmin
$finalresults = $mydata.Clone()
foreach ($row in $mydata.Rows)
{
$finalresults.ImportRow(
($row | where {
($_.login -ne "SA")
-and ($_.login -ne "MYDOMAIN\DB Admin Group")
# -and (etc) # Add other exclusions as needed
})
)
}
if ($finalresults.Count -ne 0)
{
Invoke-Sqlcmd -Query "delete [CentralAdminDatabase].[dbo].[SA_Logins];" -ServerInstance "$centralserver"
$finalresults | Write-SqlTableData -ServerInstance $centralserver -DatabaseName "CentralAdminDatabase" -SchemaName "dbo" -TableName "SA_Logins" -Force
}
Edit: fixed a couple grammar and code mistakes
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 19, 2018 at 4:59 am
Yeah, that's an approach that will work and get done what you're looking for. However, I'd strongly suggest you take a look at dbatools. It's the best way to get stuff done within PowerShell and makes a real difference in how fast you can have things set up & running. The cost is pretty good too, free.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 19, 2018 at 7:18 am
Grant Fritchey - Thursday, July 19, 2018 4:59 AMYeah, that's an approach that will work and get done what you're looking for. However, I'd strongly suggest you take a look at dbatools. It's the best way to get stuff done within PowerShell and makes a real difference in how fast you can have things set up & running. The cost is pretty good too, free.
Thank you very much! I can't believe I missed that. At the very least, this experience has given me a new appreciation (and frustration) towards powershell.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 19, 2018 at 7:32 am
Oh, the love/hate relationship with PowerShell will only deepen, on both sides.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 19, 2018 at 9:33 am
Grant Fritchey - Thursday, July 19, 2018 7:32 AMOh, the love/hate relationship with PowerShell will only deepen, on both sides.
LOL...yeah. I'm focusing on getting everything loaded into a datatable type, because I know when I get to that stage, I can push/pull data to databases more easily.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply