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 What permissions do users have on that server?

In the last post on Checking SQL Server User Role Membership with PowerShell we checked the permissions a user had across the estate, this one answers the question about all users on a server.

This is generally asked by DBAs of each other , auditors and the owners of the service

The first part of the script is very similar to the last post on Checking SQL Server User Role Membership with PowerShell but we use the EnumMembers method to display the members of the roles.

The second part – the object permissions comes with thanks to David Levy via This Link

To call it simply load the function

and a report

You can get the code here

#############################################################################################
#
# NAME: Show-SQLServerPermissions.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:06/08/2013
#
# COMMENTS: Load function for Enumerating Server and Database Role permissions or object permissions
#
# USAGE Show-SQLServerPermissions Server1
# ————————————————————————

Function Show-SQLServerPermissions ($SQLServer) {
    $server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer

    $selected = "" 
    $selected = Read-Host "Enter Selection 
                                1.) Role Membership or 
                                2.) Object Permissions"
    
    Switch ($Selected) {
        1 {
    
            Write-Host "####  Server Role Membership on $Server ############################################## `n`n"
            foreach ($Role in $Server.Roles) {
                                
                if ($Role.EnumServerRoleMembers().count -ne 0) {
                    Write-Host "###############  Server Role Membership for $role on $Server #########################`n" 
                    $Role.EnumServerRoleMembers()
                }

            }
            Write-Host "######################################################################################" 
            Write-Host "######################################################################################`n `n `n" 


            foreach ($Database in $Server.Databases) {
                Write-Host "`n####  $Database Permissions on $Server ###############################################`n" 
                foreach ($role in $Database.Roles) {
                    if ($Role.EnumMembers().count -ne 0) {
                        Write-Host "###########  Database Role Permissions for $Database $Role on $Server ################`n"
                        $Role.EnumMembers()
                    }

                }

            }
        } 

        2 {

            Write-Host "##################  Object Permissions on $Server ################################`n"
            foreach ($Database in $Server.Databases) {
                Write-Host "`n####  Object Permissions on $Database on $Server #################################`n"
                foreach ($user in $database.Users) {
                    foreach ($databasePermission in $database.EnumDatabasePermissions($user.Name)) {
                        Write-Host $databasePermission.PermissionState $databasePermission.PermissionType "TO" $databasePermission.Grantee
                    }
                    foreach ($objectPermission in $database.EnumObjectPermissions($user.Name)) {
                        Write-Host $objectPermission.PermissionState $objectPermission.PermissionType "ON" $objectPermission.ObjectName "TO" $objectPermission.Grantee
                    }
                }
            }
        }
    }
}