Powershell Add a Login to SQL Server - The Multifunctioning DBA

The Multifunctioning DBA

Jul 17 2009   8:19PM GMT

Powershell Add a Login to SQL Server



Posted by: Colin Smith
Powershell, MSSQL Server, SQL Server, Database Administration

I have a need to write a script that will check to see if a Windows Group exists on a server instance and if not then add it. I was attempting to do this using Powershell and SMO, but I was not really having any luck with that. I changed modes and I have decided to use the invoke-sqlcmd commandlet to get the task done. I use SMO to determine if the group that I want is on the server. If not then I create it. Like so:

$instance = “server\instance”

[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
#$s | Get-Member -memberType Property
#$s.logins | Get-Member
$logins = $s.Logins
$query = “CREATE LOGIN [domain\group name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]“
$dba = $logins | where{$_.Name -eq “domain\group name”}
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 “PNI\PNI SQL SYSTEMS DBAS”}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}

Not to hard and it is nice to be able to use SMO to check for the login and then Powershell to create it.

Comment on this Post


You must be logged-in to post a comment. Log-in/Register