$username = "test"
#Setup SMO Server object with conenction to self
$SQLinstance = "IT-SQL"
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLinstance
#setup variables for checking if DB exists (also used to create DB if it doesn't exist)
$dbname = $username
$datafilename = $dbname + '_Data'
$datafilepath = $s.Information.MasterDBPath + '\' + $datafilename + '.mdf'
#Check if database already exists by checking datafile path. This is the quickest way to check as it doesn't require us to enumerate through all the databases.
if (test-path $datafilepath){
Write-Warning "Database already exist for user $username. Skipping create!"
return
}
# Instantiate the database object and add the filegroups
$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
$dbfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'PRIMARY')
$db.FileGroups.Add($dbfg)
# Create the file for the system tables
$dbdatafile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($dbfg, $datafilename)
$dbfg.Files.Add($dbdatafile)
$dbdatafile.FileName = $datafilepath
$dbdatafile.Size = [double](5.0 * 1024.0)
$dbdatafile.GrowthType = 'Percent'
$dbdatafile.Growth = 10.0
$dbdatafile.IsPrimaryFile = 'True'
$dbdatafile.MaxSize = [double](100.0 * 1024.0)
# Create the file for the log
$loglogname = $dbname + '_Log'
$dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, $loglogname)
$db.LogFiles.Add($dblfile)
$dblfile.FileName = $s.Information.MasterDBLogPath + '\' + $loglogname + '.ldf'
$dblfile.Size = [double](10.0 * 1024.0)
$dblfile.GrowthType = 'Percent'
$dblfile.Growth = 10.0
# Create the database
$db.Create()
if ($db.state -eq "Existing"){
Write-host "Database for user $username successfully created"
}
else{
Write-Warning "Database for user $username did not create succesffully!!! Skipping setting permissions"
return
}
#Check if student already has a login to SQL
$logins = $s.Logins
$studlogin = $logins | where{$_.Name -eq “insearch1\$username”}
if ($studlogin -eq $null){
Write-Host "SQL Login for $username doesn't exist, creating"
$sqllogin = new-object ('Microsoft.SqlServer.Management.Smo.Login') ($s, "insearch1\$username")
$sqllogin.LoginType = "WindowsUser"
$sqllogin.Create()
$sqllogin.DefaultDatabase = $dbname
$sqllogin.Alter()
$dbuser = new-object ('Microsoft.SqlServer.Management.Smo.User') ($db, $Login.Name)
$dbuser.UserType = "SqlLogin"
$dbuser.Login = $sqllogin
$dbuser.create()
}
else{
Write-Host "SQL Login for $username already exists."
}
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE