The Multifunctioning DBA

Jul 17 2009   8:19PM GMT

Powershell Add a Login to SQL Server



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

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

 
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: