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

Viewing 1 reply thread
  • 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
      Moderator
      Topics: 3
      Replies: 113
      Points: 585
      Helping Hand
      Rank: Major Contributor

      So… what is your specific problem?

Viewing 1 reply thread
  • The topic ‘Need to design a UI using powershell having options to create loginuser,dbrole’ is closed to new replies.