Need to design a UI using powershell having options to create loginuser,dbrole

Welcome Forums General PowerShell Q&A Need to design a UI using powershell having options to create loginuser,dbrole

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
1 month, 3 weeks ago.

  • Author
    Posts
  • #167656

    Participant
    Topics: 1
    Replies: 0
    Points: -7
    Rank: Member

    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")
    
    
  • #167767

    Participant
    Topics: 2
    Replies: 54
    Points: 278
    Helping Hand
    Rank: Contributor

    So... what is your specific problem?

You must be logged in to reply to this topic.