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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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