The Multifunctioning DBA

Jul 30 2009   3:17PM GMT

Add a AD Group to MSSQL Server and add to a role using Powershell

Colin Smith Colin Smith Profile: Colin Smith

In SQL Server 2005 the Builtin Administrators group is a login, by default, to your MSSQL Server Instance and is also a member of the sysadmin role. Well this has been changed in SQL Server 2008 with good reason. I decided that I would like to add a group for my DBA Group to every SQL Server in the company and also add this group to the sysadmin role. This way I do not need to have the builtin administrators group be a member of the sysadmin group or even a login to the server. Here is how I did it.

$servers = Import-Csv “csv listing of all servers”
#########################################################
foreach ($entry in $servers)
{
    $torp = $entry.TorP
    $mon = $entry.monitor
    $machine = $entry.server
    $errorlog = $entry.errorlog
    $os = $entry.os2000
    $iname = $entry.Instance
    if ($iname -eq “Null”)
    {
        $instance = “$machine”
    }
    else
    {
        $instance = “$machine\$iname”
    }
    if ($torp -eq “Prod”)
    {
        $ServerType = “Production”
    }
    else
    {
        $ServerType = “Test”
    }
    $instance = $instance.toupper()
#########################################################

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null   
# Create an SMO connection to the instance
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance

$logins = $s.Logins
$query = “CREATE LOGIN $sqladmin FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]”
$dba = $logins | where{$_.Name -eq $sqladmin}
#$dba
if ($dba -eq $null)
{
Echo “DBA Group does not exist.`n Adding Group”
Invoke-Sqlcmd -ServerInstance $instance -Query $query
$logins = $s.Logins
$dba = $logins | where{$_.Name -eq $sqladmin}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}
if ($dba -ne $null)
{
$sroles = $s.Roles
foreach ($srole in $sroles | where{$_.name -eq “sysadmin”})
    {
    $srole.AddMember($sqladmin)

}

This script walks through the list of all my instances and adds the login using sqlcmd and then also adds that new group to the sysadmin role. Let me know if you have any questions or need anything at all.

 

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: