The Multifunctioning DBA

Sep 30 2011   4:13AM GMT

Powershell to add logins to SQL Server

Colin Smith Colin Smith Profile: Colin Smith

This is a script that I threw together for an app that needed to have 60 logins created on the sql server. We had all the ad groups created using a standard naming convention.
app_dev_groupname
was the standard that we went with so here is the code that I put together to make this easy to do and very fast. For this you must have the QAD cmdlets installed.
Function Server
{

$devserver = “dev\qa servername”    ## this server houses both the DEV and QA databases
$prodserver = “Prodservername”
$c=0
$server = read-host “What instance do you want to create the logins on? Dev, QA, or Prod?”
if ($server -eq “Prod”)
{
$c=1
$groups = get-qadgroup app_PRD_*
foreach($group in $groups)
{
$group = $group.name
Invoke-Sqlcmd -ServerInstance $prodserver -database master -query “create login [ssg\$group] from windows WITH DEFAULT_DATABASE=[master]”
}
cls
echo “All Groups have been created on $server. Please run SQL Scripts to create users and map to roles.”
}

if (($server -eq “Dev”) -or ($server -eq “QA”))
{
$c=1
$groups = @()
$QAgroups = get-qadgroup app_QA_*
$DevGroups = get-qadgroup app_DEV_*
$groups = $QAGroups + $DEVGroups
foreach($group in $groups)
{
$group = $group.name
Invoke-Sqlcmd -ServerInstance $devserver -database master -query “create login [domain\$group] from windows WITH DEFAULT_DATABASE=[master]”
}
cls
echo “All Groups have been created on $server. Please run SQL Scripts to create users and map to roles.”
}
if ($c -ne 1)
{
Echo “Incorrect Selection made. Please try again”
Server
}
}

#######MAIN######
#################
Server

So this is pretty simple and it is so much faster then manually creating all 60 groups or more on each instance.
Hope that this will help out.

 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: