Finally, my team of 1 has become 2. From now on I am not the only point of contact for SQL at my company. I tell you, it is going to be nice to go away without a laptop tucked away in my bags. To bring them on board and get his access working, I had to make sure the various security groups were set up correctly. The Database security group was configured on all SQL instances, so that worked great. There was not however a group for rdp access to the SQL instances, as I was a member of a higher level group, that gave me access to all servers, SQL or not. I created a group in AD (the benefits of being on the Middle-ware team), but I had to get the group deployed to all of my managed instances, and I was not going to add it to 102 servers manually, not when PowerShell exists in the world.
I initially approached my boss, who is an AD guru, and he said that it could be done very simply using a couple of lines of PS. He was right, It took me about 10 minutes of searching to find the right PS objects, and then about 20 minutes of playing with those objects until I found the Right combination to complete my task. To hopefully save you some time, if you are trying to accomplish the same thing, I present to you this code. I use a database for managing all of my instances, but you can easily change the code to use a server list, or array.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$MonitorInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "MyMonitoringServer"
$MonitorDB = $MonitorInstance.Databases.Item("SQLMonitor")
$domainGroup = [ADSI]("WinNT://DOMAIN/MYDOMAINSECUITYGROUP")
$ds = $MonitorDB.ExecuteWithResults("select servername from server")
foreach ($set in $ds.Tables.Rows)
$computer = $set.servername
$localGroup = [ADSI]("WinNT://$computer/Administrators")
It’s that simple. I hope this proves useful to you.
Thanks for reading.