Code I have used to populate UI and requirement is to give input from the UI window so that Login and User s can be created associated with database and assign required database role.
Code to design UI:
==================
#Loading Assemblies.
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
Import-Module -name sqlserver -DisableNameChecking
$global:htmlDocument=$null # Global variable for HTML Document Path
$global:ColumnNames=$null #Global Variable for selected Columns names
[PSCredential]$script:credential
[array]$DropDownArray = "FULL", "BULK_LOGGED", "SIMPLE"
#Form
$Form = New-Object system.Windows.Forms.Form
$Form.Size = New-Object System.Drawing.Size(1000,750)
$Form.MaximizeBox=$false
$Form.StartPosition = "CenterScreen"
$Form.FormBorderStyle = 'Fixed3D'
$Form.BackColor='white'
$Form.Text = "Database Solutions"
# Check for ENTER and ESC presses
$Form.KeyPreview = $True
$Form.Add_KeyDown({if ($_.KeyCode -eq "Escape")
{
# if escape, exit
$Form.Close()
}
})
Function Makenewform
{
$TSQLtext.Text="";
$TSQLtext1.Text="";
$Servname.Text="";
$dropdownDB.items.Clear();
$Emailaddress.Text="";
$resultLabel1.text="";
$checkbox.Checked=$false;
$checkbox1.Checked=$false;
$listBox.items.clear();
$listbox1.items.clear();
$Subjectdetails.text="";
$getDb.Enabled=$true
$dropdownDB1.Visible = $false
}
$checklistBoxnitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
$OnLoadForm_StateCorrection=
{
#Correct the initial state of the form to prevent the .Net maximized form issue
$form.WindowState = $checklistBoxnitialFormWindowState
}
#Header Application database Services Notice
$headerLabel = New-Object System.Windows.Forms.RichTextBox
$headerLabel.Location = New-Object System.Drawing.Point(15,15)
$headerLabel.Size = New-Object System.Drawing.Size(959,39)
$headerLabel.SelectionAlignment="Center"
$headerLabel.Text = "Data Solutions Database Maintenance Tool"
$headerLabel.DataBindings.DefaultDataSourceUpdateMode = 0
$headerLabel.Font= [System.Drawing.Font]::new("Arial", 14, [System.Drawing.FontStyle]::Bold)
$headerLabel.BackColor="#0070C0"
$headerLabel.WordWrap=$true
$headerLabel.ForeColor="White"
$Form.Controls.Add($headerLabel)
# Server Name Label
$Serverlabel = New-Object System.Windows.Forms.Label
$Serverlabel.Font = new-object System.Drawing.Font('Arial', 10,[System.Drawing.FontStyle]::bold)
$Serverlabel.Location = New-Object System.Drawing.Point(20,60)
$Serverlabel.Size = New-Object System.Drawing.Size(97,28)
$Serverlabel.ForeColor="#0039A6"
$Serverlabel.Text="Server Name :"
$Form.Controls.Add($Serverlabel)
# Server Name
$Servname = New-Object System.Windows.Forms.TextBox
$Servname.Font = new-object System.Drawing.Font('Arial', 10,[System.Drawing.FontStyle]::Regular)
$Servname.Location = New-Object System.Drawing.Point(120,60)
$Servname.Size = New-Object System.Drawing.Size(169,25)
$Servname.Multiline=$true
$Servname.Add_TextChanged({
#$dropdownDB.Items.clear();
#$dropdownDB.text="";
})
$Form.Controls.Add($Servname)
#Database Label
$DBlabel = New-Object System.Windows.Forms.Label
$DBlabel.Font = new-object System.Drawing.Font('Arial', 10,[System.Drawing.FontStyle]::bold)
$DBlabel.Location = New-Object System.Drawing.Point(20,100)
$DBlabel.Size = New-Object System.Drawing.Size(118,28)
$DBlabel.ForeColor="#0039A6"
$DBlabel.Text="Database Name :"
$Form.Controls.Add($DBlabel)
#Database Selected Listbox items
$listBox = new-object System.Windows.Forms.ListBox
$listBox.SuspendLayout()
$listBox.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10)
$listBox.FormattingEnabled = $true
$listBox.ItemHeight = 20;
$listBox.Location = New-Object System.Drawing.Point(325, 100);
$listBox.Size = New-Object System.Drawing.Size(100, 130);
$listBox.TabIndex = 1;
$listbox.SelectionMode = 'MultiExtended'
$Form.Controls.Add($listBox)
#Database CheckListbox items
$DBname = new-object System.Windows.Forms.CheckedListBox
$DBname.SuspendLayout()
$DBname.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::Regular, [System.Drawing.GraphicsUnit]::Point, 0);
$DBname.FormattingEnabled = $true;
$DBname.Location = New-Object System.Drawing.Point(140,100)
$DBname.Size = New-Object System.Drawing.Size(149,130)
$DBname.BorderStyle='None'
$DBname.TabIndex = 0
$DBname.CheckOnClick = $true
$DBname.TabStop = $false
$form.Controls.add($DBname)
#functionality to move the selcted columns from Database checklist to listbox.
$event_handler = {
param(
[Object] $sender,
[System.Windows.Forms.ItemCheckEventArgs ] $eventargs
)
$checklistBoxitem = $DBname.SelectedItem
if ( $eventargs.NewValue -eq [System.Windows.Forms.CheckState]::Checked )
{
[void]$listBox.Items.Add( $checklistBoxitem );
}
else
{
$listBox.Items.Remove( $checklistBoxitem );
}
}
$DBname.Add_ItemCheck($event_handler)
#function to get the Selected coulmns from list.
Function populate
{
for($i = 0; $i -lt $listbox.Items.Count; $i++)
{
[void] $listbox.SetSelected($i, $true)
}
Return $listbox.selectedItems
}
#GetDB Label
$GetDB = New-Object System.Windows.Forms.Button
$GetDB.Location = New-Object System.Drawing.Point(550,95)
$GetDB.Font = new-object System.Drawing.Font('Arial', 10,[System.Drawing.FontStyle]::Bold)
$GetDB.Size = New-Object System.Drawing.Size(130,30)
$GetDB.UseVisualStyleBackColor = $True
$GetDB.Text ="Get DB's"
$GetDB.ForeColor="#0039A6"
$GetDB.BackColor="White"
$Form.Controls.Add($GetDB)
#Event for GetDB Connection
$handler_GetDatabases_Click= {
try {
$Serv =$Servname.text
If ($Servname.text -eq "" )
{
$result=[System.Windows.Forms.MessageBox]::Show("Please enter the name of the server !")
}
else {
$results = @()
# login using SQL authentication, which means we supply the username
# and password
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$Serv"
$server.ConnectionContext.LoginSecure=$false;
<#
$script:credential = Get-Credential -Message "Please enter credentials to get the databases"
#the backslash is regular expression to remove \ (prompt will give it as default for domain)
$userName = $script:credential.UserName -replace("\\","")
#getnetworkcredential gives the passport unencrypted
$pass = $script:credential.GetNetworkCredential().password
#>
$db = "master"
$sqlQuery="select name from sys.databases;"
try
{
$Error.Clear()
$dt = Invoke-Sqlcmd -Database $db -ServerInstance $Serv -Query $SqlQuery -outputas Datatables
If ($dt[0].rows.count -gt 0)
{
# $ret=[System.Windows.Forms.MessageBox]::Show("Retrieved databases successfully", "Message")
$GetDb.Enabled=$false
$clmns=$dt[0].Columns[0].ColumnName
for($i=0;$i -lt $dt[0].Rows.Count;$i++)
{
[void]$dbname.Items.add($dt[0].Rows[$i].$clmns)
}
}
}
Catch {
if($Error)
{
[System.Windows.Forms.MessageBox]::Show($Error, "Message")
}
}
}
}
Catch {
write-host "Caught an exception:" -ForegroundColor Yellow
write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Yellow
write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Yellow
}
}
$GetDB.add_click($handler_GetDatabases_Click)
# Create a group for Database Roles that will contain your radio buttons
$MyGroupBox = New-Object System.Windows.Forms.GroupBox
$MyGroupBox.SuspendLayout()
$MyGroupBox.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$MyGroupBox.Location = '30,225'
$MyGroupBox.size = '950,200'
$MyGroupBox.ForeColor="#0039A6"
$MyGroupBox.text = "Select desired DBRole"
$form.Controls.add($MyGroupBox)
# Database Reader radio button
$RadioButton1 = New-Object System.Windows.Forms.RadioButton
$RadioButton1.SuspendLayout()
$RadioButton1.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton1.Location = '20,50'
$RadioButton1.size = '140,20'
$RadioButton1.Checked = $false
$RadioButton1.ForeColor = "Black"
$RadioButton1.Text = "db_accessadmin"
$form.Controls.add($RadioButton1)
# Database Online radio button
$RadioButton2 = New-Object System.Windows.Forms.RadioButton
$RadioButton2.SuspendLayout()
$RadioButton2.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton2.Location = '20,20'
$RadioButton2.size = '145,20'
$RadioButton2.Checked = $true
$RadioButton2.ForeColor = "Black"
$RadioButton2.Text = "db_backupoperator"
$form.Controls.add($RadioButton2)
# Database Offline radio button
$RadioButton3 = New-Object System.Windows.Forms.RadioButton
$RadioButton3.SuspendLayout()
$RadioButton3.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton3.Location = '170,20'
$RadioButton3.size = '130,20'
$RadioButton3.Checked = $false
$RadioButton3.ForeColor = "Black"
$RadioButton3.Text = "db_datareader"
$form.Controls.add($RadioButton3)
# Database Recovery Model radio button
$RadioButton4 = New-Object System.Windows.Forms.RadioButton
$RadioButton4.SuspendLayout()
$RadioButton4.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton4.Location = '320,20'
$RadioButton4.size = '200,20'
$RadioButton4.Checked = $false
$RadioButton4.ForeColor = "Black"
$RadioButton4.Text = "db_datawriter"
$form.Controls.add($RadioButton4)
# Drop Database radio button
$RadioButton5 = New-Object System.Windows.Forms.RadioButton
$RadioButton5.SuspendLayout()
$RadioButton5.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton5.Location = '540,20'
$RadioButton5.size = '130,20'
$RadioButton5.Checked = $false
$RadioButton5.ForeColor = "Black"
$RadioButton5.Text = "db_ddladmin"
$form.Controls.add($RadioButton5)
# SQL Query radio button
$RadioButton6 = New-Object System.Windows.Forms.RadioButton
$RadioButton6.SuspendLayout()
$RadioButton6.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton6.Location = '170,50'
$RadioButton6.size = '150,20'
$RadioButton6.Checked = $false
$RadioButton6.ForeColor = "Black"
$RadioButton6.Text = "db_denydatareader"
$form.Controls.add($RadioButton6)
# DBRoles assign radio button
$RadioButton7 = New-Object System.Windows.Forms.RadioButton
$RadioButton7.SuspendLayout()
$RadioButton7.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton7.Location = '690,20'
$RadioButton7.size = '150,20'
$RadioButton7.Checked = $false
$RadioButton7.ForeColor = "Black"
$RadioButton7.Text = "db_denydatawriter"
$form.Controls.add($RadioButton7)
# DBRoles assign radio button
$RadioButton7 = New-Object System.Windows.Forms.RadioButton
$RadioButton7.SuspendLayout()
$RadioButton7.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton7.Location = '690,20'
$RadioButton7.size = '150,20'
$RadioButton7.Checked = $false
$RadioButton7.ForeColor = "Black"
$RadioButton7.Text = "db_owner"
$form.Controls.add($RadioButton7)
# DBRoles assign radio button
$RadioButton7 = New-Object System.Windows.Forms.RadioButton
$RadioButton7.SuspendLayout()
$RadioButton7.Font = new-object System.Drawing.Font('Microsoft Sans Serif', 10, [System.Drawing.FontStyle]::regular, [System.Drawing.GraphicsUnit]::Point, 0);
$RadioButton7.Location = '690,20'
$RadioButton7.size = '150,20'
$RadioButton7.Checked = $false
$RadioButton7.ForeColor = "Black"
$RadioButton7.Text = "db_securityadmin"
$form.Controls.add($RadioButton7)
# Action on Clicking db_datareader role assignment
$handler_RadioButton1_Checked=
{
try
{
if ($RadioButton1.Checked)
{
$TSQLtext.Text = "USE [test]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [Test2]
GO "
}
else
{
$TSQLtext.Text = ""
}
}
catch
{
$form.close();
write-host "Caught an exception:" -ForegroundColor Yellow
write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Yellow
write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Yellow
}
}
$RadioButton1.add_CheckedChanged($handler_RadioButton1_Checked)
#The action on Clicking db_datawriter role assignment
$handler_RadioButton4_Checked=
{
try
{
if ($RadioButton4.Checked)
{
$dropdownDB1.Visible = $true
}
else
{
$dropdownDB1.Visible = $false
}
}
catch
{
$form.close();
write-host "Caught an exception:" -ForegroundColor Yellow
write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Yellow
write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Yellow
}
}
$RadioButton4.add_CheckedChanged($handler_RadioButton4_Checked)
# Add all the GroupBox controls on one line
$MyGroupBox.Controls.AddRange(@($Radiobutton1,$RadioButton2,$RadioButton3,$RadioButton4,$RadioButton5,$RadioButton6,$RadioButton7,$checkbox,$checkbox1,$checkbox2,$dropdownDB1))
#$SubmitButton.Add_click($handler_SubmitButton_Click)
#Submit Button
$SubmitButton = New-Object System.Windows.Forms.Button
$SubmitButton.Text = "Submit"
$SubmitButton.Font= [System.Drawing.Font]::new("Arial", 10,[System.Drawing.FontStyle]::Bold)
$SubmitButton.DataBindings.DefaultDataSourceUpdateMode = 0
$SubmitButton.Location = New-Object System.Drawing.Point(50,656)
$SubmitButton.Size = New-Object System.Drawing.Size(115,32)
$SubmitButton.BackColor='#0070C0'
$SubmitButton.ForeColor="white"
$SubmitButton.Cursor = [System.Windows.Forms.Cursors]::Hand
$Form.Controls.Add($SubmitButton)
$SubmitButton.add_click($handler_SubmitButton_Click)
#Button Close click
$handler_CloseButton_Click ={
$form.close();
}
$CloseButton = New-Object System.Windows.Forms.Button
$CloseButton.Location = New-Object System.Drawing.Point(645,656)
$CloseButton.Size = New-Object System.Drawing.Size(115,32)
$CloseButton.Font= [System.Drawing.Font]::new("Arial", 10, [System.Drawing.FontStyle]::Bold)
$CloseButton.DataBindings.DefaultDataSourceUpdateMode = 0
$CloseButton.Name = "button4"
$CloseButton.BackColor='#0070C0'
$CloseButton.ForeColor="white"
$CloseButton.TabIndex = 0
$CloseButton.Text = "Close"
$CloseButton.Cursor = [System.Windows.Forms.Cursors]::Hand
$CloseButton.add_Click($CloseButton_OnClick)
$form.Controls.Add($CloseButton)
$CloseButton.add_click($handler_CloseButton_Click)
$form.SuspendLayout()
#Finalize Form and Show Dialog
$form.ResumeLayout($false)
$form.Add_Shown({$Form.Activate();$Servname.focus();})
[void] $form.ShowDialog()
Script to create LOgin and user and assigned required DB role
==========================================
$SqlServer = "sql-d-16p3027"
$SqlDBName = "test"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer)
# get all of the current logins and their types
$SqlServer.Logins |
Select-Object Name, LoginType, Parent
# create a new login by prompting for new credentials
$NewLoginCredentials = Get-Credential -Message "Enter credentials for the new login"
$NewLogin = New-Object Microsoft.SqlServer.Management.Smo.Login($SqlServer, $NewLoginCredentials.UserName)
$NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
$NewLogin.Create($NewLoginCredentials.Password)
# create a new database user for the newly created login
$NewUser = New-Object Microsoft.SqlServer.Management.Smo.User($SqlServer.Databases[$SqlDBName], $NewLoginCredentials.UserName)
$NewUser.Login = $NewLoginCredentials.UserName
$NewUser.Create()
$NewUser.AddToRole("db_datareader")