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


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


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


I do the same for the database roles


I then call it as follows


And get a handy report


Here is the full code


11 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

  3. Hi , thanks

    How should we order by CreateDate, DateLastModified for last 7 days?

    if($role -match ‘sysadmin’)
    Write-Host “SysAdmins found: $($SQLUser.Name)” -ForegroundColor Yellow;
    $SQLUser | Select-Object
    @{label = "SQLServer"; Expression = {$SQLSvr}},

    @{label = “CurrentDate”; Expression = {(Get-Date).ToString(“yyyy-MM-dd”)}},
    @{label = "System TypeName"; Expression = {$ds.Tables[0].Rows[$i]["SystemTypeName"]}},

    @{label = “Environment Name”; Expression = {$ds.Tables[0].Rows[$i][“EnvironmentName”]}},
    @{label = "Role Name"; Expression = {$ds.Tables[0].Rows[$i]["RoleName"]}},
    Name, LoginType, CreateDate, DateLastModified, IsDisabled, IsLocked;

    if($SQLUser.DateLastModified -ge $dateToCompare)
    $SysAdminsAddedInReportingPeriod+=$SQLUser | Select-Object

    @{label = “SQLServer”; Expression = {$SQLSvr}},
    @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}},

    @{label = “System TypeName”; Expression = {$ds.Tables[0].Rows[$i][“SystemTypeName”]}},
    @{label = "Environment Name"; Expression = {$ds.Tables[0].Rows[$i]["EnvironmentName"]}},

    @{label = “Role Name”; Expression = {$ds.Tables[0].Rows[$i][“RoleName”]}},
    Name, LoginType, CreateDate, DateLastModified, IsDisabled, IsLocked;

  4. Pingback: SQL login object permissions via PowerShell | SQL DBA with A Beard

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.