Checking SQL Server User Role Membership with PowerShell

As you know, I love PowerShell!

I use it all the time in my daily job as a SQL DBA and at home whilst learning as well.

Not only do I use PowerShell for automating tasks such as Daily Backup Checks, Drive Space Checks, Service Running Checks, File Space Checks, Failed Agent Job Checks, SQL Error Log Checks, DBCC Checks and more but also for those questions which come up daily and interfere with concentrating on a complex or time consuming task.

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever I need them. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which roles is this user a member of?

Obviously it is not normally asked exactly like that but for a DBA that is the question. Following good practice we try to ensure that database permissions are granted by role membership and each role is creatd with the minimum amount of permissions required for successful execution of the task involved. So I only concentrate on Role Membership for this script. The script in the next post will deal with Object permissions.

This question can come from Support Desks when they are investigating a users issue, Developers when they are testing an application as well as audit activities.

I start by getting the list of servers from my text file and creating an array of logins for each domain as I work in a multi domain environment

Edit April 2015 – Whilst I still use this technique in my presentations, I have found a more useful method of doing this nowadays. I have a DBA database which holds information about all of the servers and databases that we have. This enables me to get a list of servers that I wish to check by using Invoke-SQLCMD2 and passing in a query to get the servers that I require. This enables me to filter by Live/Dev/Test Servers or by Operating System or by SQL Version or any other filter I wish to add

image

Then loop through the list of servers and check if the login exists on that server

image

To check which Role the user is a member of I use the EnumMembers method and assign that to an array and then check if the user exists in the array

image

I do the same for the database roles

image

I then call it as follows

image

And get a handy report

image

Here is the full code

 

5 thoughts on “Checking SQL Server User Role Membership with PowerShell

  1. Pingback: (SFTW) SQL Server Links 06/09/13 • John Sansom

  2. Pingback: Checking SQL Server User Role Membership with PowerShell - SQL Server - SQL Server - Toad World

Please feel free to comment on this post. All comments are moderated first before appearing on the site