Blog

May 22
Audit All SQL User Security with PowerShell
I actually wrote this back in 2012 and posted it to the Alaska SharePoint User Group Blog.  But since I just posted a script about how to find all the SysAdmins on a SQL instance, I figured I should share this script, too, since it does all that and more.  Note that this script only dumps the output to console, not CSV.  I might get around to changing that someday.
 
Note: use this script at your own risk.  I don't think it will hurt anything, since it's just reading configuration, but it could run for a long time and possibly impact performance.
 
----
 

How to Tell Who Has Access to What in SQL Server

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:    

  • Each Login (Windows or SQL)
    • Its Server Roles
    • Every database to which the login is mapped
    • The associated "Database User" for this login
      • Its Database roles
      • Any explicitly assigned permissions    

This is not an "all encompassing" look at every piece of security, but it does give a pretty comprehensive look at who has access to what.  I wrote a script because gathering this information manually would be very, very time consuming.  As far as I know, there is no function in SQL Server to collect all this information into one place.  I guess I could have used a series of TSQL scripts to pull the info out by other means, but I am on a PowerShell kick, and I wanted to do it this way. 

Besides, using my method, you can automate the process and audit multiple instances at once. Sound like fun?  

I wrote this quick-and-dirty so there is no error handling. There is, of course, no warranty expressed or implied and you should use caution when running this (or any other script you find on the Internet). That said, all of my calls to SQL are read-only, so I don't think you'll have any problems.  

I'll go ahead and paste the script and then explain it below:  

       # ========================================================================================================  
 #   
 # NAME: InventorySQLUserSecurity.ps1  
 #   
 # Comment: This script is designed to "walk" the security structure of each SQL instance to find:  
 #   
 #  · Each Login (Windows or SQL)  
 #     o Its Server Roles  
 #     o Every database to which the login is mapped  
 #        § The associated "Database User" for this login  
 #          · Its Database roles   
 #          · Any explicitly assigned permissions  
 #  
 # The script is hard-coded to locate a text file with a list of instance names (Hostname\instancename format).  
 # The text file should contain one instance per line.   
 # All output is dumped to console.  
 # About the only error checking included is to check whether a database is online before analyzing it.  
 # ========================================================================================================  
 Function GetDatabaseUser($Dbase)  
 {  
  if ($dbase.status -eq "Normal")  
   {$users = $Dbase.users | where {$_.login -eq $SQLLogin.name}   
     foreach ($u in $users)  
     {  
       if ($u)  
         {    
          write-host $spc5 "===== Database: " $Dbase.Name  
          write-host $spc5 "Login's Database Mappings: "  
          #$u | select-object name, login, parent, createdate, datelastmodified, DefaultSchema, HasDBAccess  
           Write-host $spc10  "Name       : " $u.Name  
           Write-host $spc10  "Login      : " $u.Login  
           Write-host $spc10  "CreateDate    : " $u.createdate  
           Write-host $spc10  "DateLastModified : " $u.datelastmodified  
           Write-host $spc10  "DefaultSchema  : " $u.DefaultSchema  
           Write-host $spc10  "HasDBAccess   : " $u.HasDBAccess  
          write-host $spc5 "Database Roles for this DBUser:"   
          $DBRoles = $u.enumroles()   
          if ($DBRoles)  
           {$spc10 + $DBRoles}  
          Else  
         {Write-host $spc10 "None."}  
         write-host $spc5 "Explicit Database Permissions for this DBUser:"   
         $DBExplict = $Dbase.EnumObjectPermissions($u.Name) | select-object objectname, permissiontype, permissionstate   
         if ($DBExplict)  
           {$spc10 + $DBExplict}  
         Else  
           {Write-host $spc10 "None."}  
      }  
     #else   
     #{Write-host $spc10 "None."}  
    } # Next user in database  
   }  
   #else  
   #Skip to next database.  
 }  
 #Main Program starts here  
 [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
 [string] $spc5 = "   "  
 [string] $spc10 = "     "  
 foreach ($SQLsvr in get-content "C:\IT\Scripts\Instances.txt")  
 {  
   $svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLsvr   
   write-host "================================================================================="  
   write-host "-----"  
   write-host "-----     SQL Instance: " $svr.name  
   write-host "-----"  
   write-host "================================================================================="  
   Write-host "SQL Version:" $svr.VersionString          
   Write-host "Edition:" $svr.Edition             
   Write-host "Login Mode:" $svr.LoginMode  
   $SQLLogins = $svr.logins  
   foreach ($SQLLogin in $SQLLogins)  
   {  
     write-host "--------------------------------------------------"  
     write-host "----- Login: " $SQLLogin.name   
     write-host "--------------------------------------------------"  
     write-host $spc5 "Login Type: " $SQLLogin.LoginType  
     write-host $spc5 "Created: " $SQLLogin.CreateDate  
     write-host $spc5 "Default Database: " $SQLLogin.DefaultDatabase  
     write-host $spc5 "Has Access to this instance: " $SQLLogin.HasAccess  
     write-host "   ----------------------------------------------------------------------------"  
     write-host $spc5 "Server Roles for:" $SQLLogin.name   
     $SQLRoles = $SQLLogin.ListMembers()  
     if ($SQLRoles)   
       {$spc10 + $SQLRoles}  
     else  
       {Write-host $spc10 "None."}  
     Write-host $spc5 "This login maps to database users in the following databases:"  
     if ($SQLLogin.EnumDatabaseMappings())  
       {Write-host " "  
       foreach ( $DB in $svr.Databases)   
         {   
         GetDatabaseUser($DB)  
         } # Next Database  
       }  
     Else  
       {Write-host $spc10 "None."}   
   } # Next Login  
 } # Next Server  

 Download source file: InventorySQLUserSecurity.txt (rename to .ps1)

The basic flow of the script is:

  • Open the Instances.txt file, which is a simple text file with one instance per line.
    • The script will loop through each of the instances and prepare a report for each of them.
  • Provide some basic information about the instance.
  • For each Instance:
    • Connect to the instance's "Logins" collection.
    • For each Login:
      • Provide basic information about the login:
        • Whether it is SQL or Windows security
        • When the login was created
        • What the login's default database is
        • Whether or not the login currently has database access (i.e. disabled or not)
        • List any server roles
        • Find if the Login is associated with any database users.
          • If the Login has any database users
          • (i.e. the $SQLLogin.EnumDatabaseMappings() is not null) then:
            • For Each Database:
              • If the database is online (i.e. its status is "Normal") then:
                • Show the SQL Login and its associated Database Username
                • When it was created/modified
                • What its default schema is
                • Whether or not it has access to the database (i.e. denied or granted)
                • The Database user's DB roles
                • Any explicitly granted permissions (e.g. to tables or stored procedures)
            • Next Database
      • Next Login
  • Next SQL instance

The output of this is a somewhat-formatted and fairly detailed look at what each person has access to.

I'm sure I could have done this a myriad of other ways, but I was in a hurry.

Note that, depending on the number of logins and databases, this could chug for quite a while and output a lot of information (one instance I audited gave a 374-page report). The output is dumped to the console, which in PowerShell ISE is not a problem because it has a very large buffer. The next version of this will have the option to write to a text file, which isn't much more difficult; I just didn't have the reference in front of me on how to do that. So what I've been doing is copy/pasting the text into Word and finessing it from there.

My next version of this will probably be database-connected. I have been working on a PowerShell-based solution to inventory all SQL Servers on a network, including information on Hosts, SQL instances, Databases, Filegroups, and files; the end result is to have a comprehensive look at who owns what data and what resources the Business Units are consuming (e.g. Do the HR division's databases take up more disk space than the Administration division's databases?) This security audit data will be grafted into the database. If anyone's interested I can write about that, too.  

May 22
Find all SQL SysAdmins with PowerShell

​I wrote a PowerShell script to find all the logins with the SysAdmin role and dump the output to a CSV.  The script runs against a list of servers you provide in a text file (one instance per line).

 # ===================================================================================== 
 #   
 # NAME: InventorySQLAdmins.ps1  
 #   
 # Comment: This script is designed to "walk" the security structure of each SQL instance to find:  
 #   
 #  · Each Login (Windows or SQL)  
 #     o Its Server Roles  
 #     o If the login is a SysAdmin, it will be added to the output array $rows  
 #     o The array stores the name of the current SQL instance, and the login name.  
 #  
 # This is handy for building a list of SysAdmins on all your SQL instances, which is definitely something   
 # you should keep track of.   
 #  
 # The $rows output is saved to a CSV file.  
 #  
 # ===================================================================================== 
 #Main Program starts here  
 [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
 $rows = @()  
 foreach ($SQLsvr in get-content "C:\IT\Scripts\Instances.txt")  
 {  
   $svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLsvr   
   $SQLLogins = $svr.logins  
   foreach ($SQLLogin in $SQLLogins)  
   {  
     $SQLRoles = $SQLLogin.ListMembers()  
     if ($SQLRoles.Contains("sysadmin"))   
       {  
       $rows += $SQLLogin | select-object Parent,name  
       }   
   } # Next Login  
 } # Next Server  
 $Rows | export-csv "C:\IT\Scripts\SQLAdmins.csv"  
Download Source file: InventorySQLAdmins.txt (rename with .ps1) 
 
Apr 13
7-16-15: Windows Clustering for the DBA (Part 2)

Topic for the July 2015 Meeting:

Rescheduled: Windows Clustering for the DBA (Part 2)

You need to implement AlwaysOn Failover Clustering or Availability Groups, but need help with the Windows Clustering part.  This two-part session will guide you to success.  We'll talk about how to plan your network, DNS, Active Directory, and storage. 

I'll provide you with a check list you can use to plan your implementation.  We'll see how to choose the right quorum model for a robust failover configuration that ensures your database is always available.

This is part 2 of a 2-part series.  Part 1 will be held on June 11th, 2015.  Both sessions will be recorded and available on the AlaskaSQL YouTube channel.

Speaker: Ryan J. Adams (SQL Server MVP)

Ryan Adams is a SQL Server MVP and has worked for Verizon for 17 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure.  He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He is also a PASS Regional Mentor and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP  

 

Where: 

3000 C Street, Suite 210, Anchorage, AK  99503   

When:

WebEx Info:

  • Password: alaskasql

 Add recurring meeting to your Calendar: AlaskaSQL_Meeting.ics

Apr 13
6-11-15: Windows Clustering for the DBA (Part 1)

Topic for the June 2015 Meeting:

Windows Clustering for the DBA (Part 1)

You need to implement AlwaysOn Failover Clustering or Availability Groups, but need help with the Windows Clustering part.  This two-part session will guide you to success.  We'll talk about how to plan your network, DNS, Active Directory, and storage. 

I'll provide you with a check list you can use to plan your implementation.  We'll see how to choose the right quorum model for a robust failover configuration that ensures your database is always available.

This is part 1 of a 2-part series.  Part 2 will be held on July 9th, 2015.  Both sessions will be recorded and available on the AlaskaSQL YouTube channel.

Speaker: Ryan J. Adams (SQL Server MVP)

Ryan Adams is a SQL Server MVP and has worked for Verizon for 17 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure.  He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He is also a PASS Regional Mentor and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP 

Where: 

3000 C Street, Suite 210, Anchorage, AK  99503   

When:

WebEx Info:

  • Password: alaskasql

 Add recurring meeting to your Calendar: AlaskaSQL_Meeting.ics

Apr 06
5-14-15: Common SQL Server Mistakes

Topic for the May 2015 Meeting:

Common SQL Server Mistakes

Making mistakes is natural, but learning from them and avoiding them in future takes effort. There's no substitute for experience or the shared wisdom of others to help you learn what mistakes to avoid. In this session, Tim will share with you a ton of what he's learned over the years from working and consulting at multiple Fortune-level companies.

You'll hear about real-world environments he has worked on where there was a high impact from administrators making mistakes in how SQL Server was configured and administered. You'll learn many proven and accepted best practices for installing, configuring and supporting SQL Server to help your environment run as smoothly as possible and you'll leave with a checklist of items to take home to make sure your SQL Server environment is configured properly.

Tim-Radney-SQLSkills.jpgSpeaker: Tim Radney, Principal Consultant, SQLSkills.com

Tim is a SQL Server MVP. He has presented at PASS, SQL Saturdays, user groups and numerous webinars. In addition Tim runs the Columbus GA SQL Users Group, is a PASS Regional Mentor and was named a PASS Outstanding Volunteer in 2012. He's married with three children and has a passion for electronics. He also farms chickens (for eggs) and tilapias in his spare time.

Email: Tim@SQLskills.com 
Website: http://www.SQLskills.com 

Full Bio: http://www.sqlskills.com/about/tim-radney/
Blog: http://www.SQLskills.com/blogs/tim
Twitter: http://twitter.com/TRadney
Microsoft MVP since 2014

Download Tim's Slides here.

​Watch now on Youtube​: 
 


Apr 06
4-9-15: PowerBI and Azure Machine Learning

Topic for the April 2015 Meeting:

PowerBI and Azure Machine Learning​

Dave Gollob has been working with a small Alaska Juvi data set (mart) for quite some time. Watch as Dave takes traditional analytics using Juvi trends into the world of machine learning and data mining. Dave will take the results of the models he has created and surface those analytics in the new Power BI Preview leveraging a hybrid strategy and Analysis Services Tabular Model. This will be an end to end session. You will come away with knowledge of the new Azure Machine Learning, Power BI Preview and unique ways to look at the data you already have. Put your "Data Hacker" hat on and think about the possibilities!

Speaker: Dave Gollob

Dave is Platform Architect covering State and Local Government Accounts for mountain, pacwest and desert states. Dave is active in the Microsoft community and has contributed analytics strategies and approaches to his peers and customers during his many years at Microsoft. Dave is a well-seasoned speaker, speaking at PASS, Launch Events, Executive Briefings and on several industry panel forums. Dave loves the creative effort that goes into creating unique assets to show to customers and has developed numerous solutions, including: Airport traffic, Workmen's Comp, Juvi Offense, Morbidity correlation, Public Vehicle Accidents, and more. In Dave's spare time he enjoys being with his wife and kids, mountain biking, skiing, hiking, and anything that keeps him outdoors..   

Watch Now on YouTube​:

 


Mar 09
3-12-15: Backup and Restore

This month's topic will be about the most mundane of a DBA's duties: Backing up and restoring a database. 

At least, it should be mundane.  You don't want backups and restores to be exciting, for example at two in the morning when you find out that the backups from the last six months are no good.  ​

In this session, Greg Burns will talk about:

  • The Basics of Backup and Restore
  • Meeting an SLA
  • 3rd Party Backup solutions
  • Maintenance Plans
  • Scripting a Backup / Restore
  • Centralizing your SQL Agent jobs
  • Ola Hallengren's Backup script

Target audience: IT Admins, DBAs, and Developers.  This will be a broad topic ranging from the 100 level to the 300 level. 

Speaker: Greg Burns

Greg Burns has been working as a SQL Server DBA (and sometime developer) since 1997.  He has worked with SQL Server in some way nearly every day, from server builds and migrations to database design and reporting.  He is a SQL PASS chapter leader for the Alaska SQL User Group (and also runs the Alaska SharePoint User Group).  He currently works as a senior consultant for Network Business Systems in Anchorage, AK.   

Download the SQL Script used in this presentation

After watching the video, I have realized there are a couple of mistakes.  I normally put a lot of time and effort into a presentation and make sure all the information is accurate, but this time I winged it a little and got a couple of facts wrong.  Although I think it's mostly good, I'd rather not leave the video out there and possibly lead people astray.

Here are some links I mentioned during the meeting: 

Beginner:

Intermediate / Advanced: 



Feb 23
NetApp and SQL Best Practices - Free Technical Seminar & Breakfast March 24th

Hi All,

Just passing this along.  My employer, Network Business Systems, is bringing up an expert to speak about best practices for SQL Server and NetApp storage.  It's a free event with a continental breakfast and prizes.

 

We're bringing a NetApp SQL expert and evangelist to Alaska to discuss everything you always wanted to know about how to manage SQL on NetApp storage.  Invite not only storage and server administrators but please include DBAs and developers.  Pat is a fascinating speaker and can address each of their areas of interest. 

 

Best practices, backup, recovery, tuning and performance acceleration, especially using cache/SSDs, can be done a number of ways depending on the SQL, Windows or NetApp operating system versions and the protocols (iSCSI, FC, NFS, CIFS, RDM, etc.) in use.   Pat is an expert; can address those options and has done extensive testing in VMware and HyperV environments with SQL 2012 and 2014.  Plus he can shine some light on the new features in SQL 2014 and SMB3.

  • When:  Anchorage, Tuesday March 24th, 8 am to 10:30 am
  • Where:  3000 C Street in Downstairs Conference Room. 

Food and prizes plus an update on NetApp hardware and software.

Click to Register!

More Info 

Feb 10
2-12-15: Lightning Talks - SSRS / Kerberos

February 12, 2015 Meeting

 

We'll be meeting at the Network Business Systems office in their conference room at their new Midtown location:

3000 C Street, Suite 210

Anchorage, Alaska  99503 

Click here for directions.

 

Lightning Talks 

This month we have two speakers:

  • Nicholas Smith, who will be talking about SQL Server Reporting Servicesn Tips and Tricks
  • Greg Burns, who will be talking about Setting up Kerberos for SQL Server 

Speaker: Nicholas Smith

Nicholas Smith is an Analyst Programmer IV working for the State of Alaska Department of Public Safety.  He has years of experience working with SQL reporting and administration. 

Speaker: Greg Burns

Greg Burns has been working as a SQL Server DBA (and sometime developer) since 1997.  He has worked with SQL Server in some way nearly every day since then, from server builds and migrations to database design and reporting.  He is the chapter leader for the Alaska SQL User Group (and also the Alaska SharePoint User Group).  He currently works as a senior consultant for Network Business Systems in Anchorage, AK.   

Watch now on YouTube:
 
Feb 10
Key Features in the New Power BI Service Announced

​Some interesting changes are coming to the PowerBI tools, aimed at making them more powerful and independent of Excel and SharePoint.  Blogger Christopher Woodill at MicrosoftTrends.com has an excellent writeup here:

http://www.microsofttrends.com/2015/01/28/key-features-in-the-new-power-bi-service-announced/

But I'll give you the high points:

  • Power BI is going Freemium for Consumers, Discounted for Businesses
  • Power BI is being Disconnected from Excel
  • Power BI is being Disconnected from Office 365 and SharePoint
  • Power BI will have New Visualizations
  • Power BI is still Cached Data and Refreshes are Limited
  • It's all about the Connectors:
    • In addition to the current collection of standard databases, NoSQL data sources, Azure Services and HDInsight...  Microsoft has announced that new Connectors for GitHub, Marketo, Dynamics CRM, SalesForce, SendGrid and Zendesk are coming
  • The Weakest Link Still Seems to be the Data Management Gateway

 

If you want to try the preview for the new PowerBI Designer, it's available here: http://powerbi.com/dashboards/downloads/designer/

1 - 10Next