PowerShell.com
Library » Script Library » SQL Server » Slideshow

SQL Server Slideshow

  1. cls 
  2. $server = 'ENTER SERVER NAME HERE' 
  3.  
  4. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
  5.  
  6. #this only looks at the default instance 
  7. $cn = new-object system.data.SqlClient.SqlConnection("Data Source=MSSQLSERVER;Integrated Security=SSPI;Initial Catalog=master"); 
  8. $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server 
  9.      
  10. #$val =$s.Logins | select Name, DefaultDatabase  
  11.  
  12.  
  13. function getRoles($roles){ 
  14.     if($roles.EnumRoles().Count -eq 0){'<h4><b>NO ROLES<b/></h4>'} 
  15.      
  16.     foreach($role in $roles.EnumRoles()){ 
  17.         $role + '<br/>' 
  18.     } 
  19. } 
  20.  
  21.  
  22. function buildADRow($rp){ 
  23.     $mep = $r | select  Login  
  24.     $inGroup = (Get-QADGroupMember $mep.Login -Enabled -Indirect -SizeLimit 18000 ) 
  25.     foreach($account in $inGroup){ 
  26.         '<tr>' 
  27.             '<td></td>' 
  28.             '<td>' + $account.NTAccountName + '</td>' 
  29.             '<td>' + (getRoles($rp)) + '</td>' 
  30.         '</tr>' 
  31.     } 
  32. } 
  33.  
  34. function buildRow($rp){ 
  35.     $t= $rp | select  Name, Login 
  36.     '<tr>' 
  37.         '<td  colspan=2>'  
  38.             if($t.Login -eq ''){ 
  39.                 $t.Name + '</td>' 
  40.             }else{ 
  41.                 $t.Login +'</td>' 
  42.             } 
  43.         '<td>' + (getRoles($rp)) + '</td>' 
  44.     '</tr>'     
  45. } 
  46.  
  47.  
  48. $dbs = $s.Databases 
  49. '<html><style type="text/css">h4 {color:red;}</style><body>' 
  50.     foreach ($db in $dbs) { 
  51.         '<br/><br/><br/>' 
  52.            $dbname = $db.Name 
  53.            $users = $db.Users 
  54.         '<table border=1 width=100%>' 
  55.         '<tr> 
  56.             <td colspan=3 bgcolor=#C0C0C0><b>' + $dbname +'</b></td> 
  57.         </tr>' 
  58.         '<tr> 
  59.             <td width=25%><b>SQL Account</b></td> 
  60.             <td width=25%><b>Group Member</b></td> 
  61.             <td width=25%><b>Rights</b></td> 
  62.         </tr>'  
  63.          
  64.             if($users.get_Count() -cge 1){ 
  65.                    foreach($r in $users){ 
  66.                            if($r.get_HasDBAccess() -eq 'True'){ 
  67.                                   if($r.LoginType -contains 'WindowsGroup'){ 
  68.                                     buildRow($r) 
  69.                                     buildADRow($r) 
  70.                                } 
  71.                                else{ 
  72.                                        buildRow($r) 
  73.                                } 
  74.                         } 
  75.                    } 
  76.                } 
  77.         '</table>'     
  78.     } 
  79. '</body></html>' 
 
Loading...
View all files
Concentrated Tech NSoftware Dell Compellent Sponsored by Idera and Concentrated Tech and NSoftware and Dell Compellent
Copyright 2011 PowerShell.com. All rights reserved.