List Databases (and Properties) on SQL Server with PowerShell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Todays question which I often get asked is What databases are on that server?

This is often a follow up to a question that requires the Find-Database script. It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.

A simple quick and easy question made simpler with this function which can also be called when creating documentation

image

Simply call it with Show-DatabasesOnServer SERVERNAME and use the results as you need

image

This only shows you the name but if you need more information about your databases then have a look and see what you require.

Use Get-Member to see what is there. I ran the following code to count the number of Properties available for Databases (Using PowerShell V3 on SQL Server 2012 SP1 11.0.3350.0 )

image

154 Properties that you can examine and that is just for databases:-)

Picking out a few properties you could do something like this

image

If you want aliases for your column headings you will need to add a bit of code to the select.

For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.

So select Name becomes

select @{label="Database Name";Expression={$_.Name}}

The Column Heading is Database Name and the data is the Name property

and select Size becomes

Select @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}

The Column Heading is Size GB and the data is the Size property divided by 1024 to 3 decimal places

then your code would look like this

$srv.databases|select @{label="Server";Expression={$_.Parent.name}},` 
@{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,` 
RecoveryModel, @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}|` 
Format-Table -Wrap –AutoSize

and the results

image

and here is the full code

<#PSScriptInfo

.VERSION 1.0

.GUID 48bf0316-66c3-4253-9154-6fc5b28e482a

.AUTHOR Rob Sewell

.DESCRIPTION Returns Database Name and Size in MB for databases on a SQL server
      
.COMPANYNAME 

.COPYRIGHT 

.TAGS SQL, Database, Databases, Size

.LICENSEURI 

.PROJECTURI 

.ICONURI 

.EXTERNALMODULEDEPENDENCIES 

.REQUIREDSCRIPTS 

.EXTERNALSCRIPTDEPENDENCIES 

.RELEASENOTES

#>
<#
    .Synopsis
    Returns the databases on a SQL Server and their size
    .DESCRIPTION
    Returns Database Name and Size in MB for databases on a SQL server
    .EXAMPLE
    Show-DatabasesOnServer

    This will return the user database names and sizes on the local machine default instance
    .EXAMPLE
    Show-DatabasesOnServer -Servers SERVER1

    This will return the database names and sizes on SERVER1
    .EXAMPLE
    Show-DatabasesOnServer -Servers SERVER1 -IncludeSystemDatabases

    This will return all of the database names and sizes on SERVER1 including system databases
    .EXAMPLE
    Show-DatabasesOnServer -Servers 'SERVER1','SERVER2\INSTANCE'

    This will return the user database names and sizes on SERVER1 and SERVER2\INSTANCE
    .EXAMPLE
    $Servers = 'SERVER1','SERVER2','SERVER3'
    Show-DatabasesOnServer -Servers $servers|out-file c:\temp\dbsize.txt

    This will get the user database names and sizes on SERVER1, SERVER2 and SERVER3 and export to a text file c:\temp\dbsize.txt
    .NOTES
    AUTHOR : Rob Sewell http://sqldbawithabeard.com
    Initial Release 22/07/2013
    Updated with switch for system databases added assembly loading and error handling 20/12/2015
    Some tidying up and ping check 01/06/2016
    
#>

Function Show-DatabasesOnServer 
{
    [CmdletBinding()]
    param (
        # Server Name or array of Server Names - Defaults to $ENV:COMPUTERNAME
        [Parameter(Mandatory = $false, 
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true, 
            Position = 0)]
        $Servers = $Env:COMPUTERNAME,
        # Switch to include System Databases
        [Parameter(Mandatory = $false)]
        [switch]$IncludeSystemDatabases
    )
    [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
    foreach ($Server in $Servers) {
        if ($Server.Contains('\')) {
            $ServerName = $Server.Split('\')[0]
            $Instance = $Server.Split('\')[1]
        }
        else {
            $Servername = $Server
        } 

        ## Check for connectivity
        if ((Test-Connection $ServerName -count 1 -Quiet) -eq $false) {
            Write-Error "Could not connect to $ServerName - Server did not respond to ping"
            $_.Exception
            continue
        }
    
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server

        if ($IncludeSystemDatabases) {
            try {
                $Return = $srv.databases| Select Name, Size
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        else {
            try {
                $Return = $srv.databases.Where{$_.IsSystemObject -eq $false}| Select Name, Size
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        Write-Output "`n The Databases on $Server and their Size in MB `n"
        $Return
    }
}

Dropping SQL Users with PowerShell

As you may have noticed, I love PowerShell!

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 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 post is not about a question but about a routine task DBAs do. Dropping Logins

Whilst best practice says add users to active directory groups, add the group to roles and give the roles the correct permissions there are many situations where this is not done and DBAs are required to manually remove logins. This can be a time consuming task but one that is essential. There was a time at MyWork when this was achieved via a script that identified which servers had a users login and the task was to connect to each server in SSMS and remove the user from each database and then drop the server login. As you can imagine it was not done diligently. Prior to an audit I was tasked with ensuring that users that had left MyWork did not have logins to any databases. It was this that lead to the Checking for SQL Logins script and to this one

It starts exactly the same as the Checking for SQL Logins script by grabbing the list of SQL Servers from the text file and creating an array of user names including all the domains as I work in a multi-domain environment

image

Then iterate through each database ignoring those that may need special actions due to the application and call the drop method

image

Repeat the process for the servers and send or save the report as required. Simple and easy and has undoubtedly saved me many hours compared to the previous way of doing things 🙂

image

IMPORTANT NOTE

This script will not delete logins if they have granted permissions to other users. I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped

This script can be found

#############################################################################################
#
# NAME: Drop-SQLUsers.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:06/08/2013
#
# COMMENTS: Load function to Display a list of server, database and login for SQL servers listed 
# in sqlservers.txt file and then drop the users
#
# I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped
#
# Does NOT drop Users who have granted permissions
#BE CAREFUL

Function Drop-SQLUsers ($Usr) {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    # Suppress Error messages - They will be displayed at the end
    $ErrorActionPreference = "SilentlyContinue"
    # cls

    # Pull a list of servers from a local text file

    $servers = Get-Content 'c:\temp\sqlservers.txt'

    # Create an array for the username and each domain slash username

    $logins = @("DOMAIN1\$usr", "DOMAIN2\$usr", "DOMAIN3\$usr" , "$usr")

				Write-Output "#################################"
    Write-Output "Dropping Logins for $Logins" 


    #loop through each server and each database and 
    Write-Output "#########################################"
    Write-Output "`n Database Logins`n"  
    foreach ($server in $servers) {      
        if (Test-Connection $Server -Count 1 -Quiet) {	
            $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
            #drop database users
            foreach ($database in $srv.Databases) {
                if ($database -notlike "*dontwant*") {
                    foreach ($login in $logins) {
                        if ($database.Users.Contains($login)) {
                            $database.Users[$login].Drop();
                            Write-Output " $server , $database , $login  - Database Login has been dropped" 
                        }
                    }
                }
            }
        }
    }
    
    Write-Output "`n#########################################"
    Write-Output "`n Servers Logins`n" 
      
    foreach ($server in $servers) {      	
        if (Test-Connection $Server -Count 1 -Quiet) {
            $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
            #drop server logins
            foreach ($login in $logins) {
                if ($srv.Logins.Contains($login)) { 
                    $srv.Logins[$login].Drop(); 
                    Write-Output " $server , $login Login has been dropped" 
                }
            }
        }
    }
    Write-Output "`n#########################################"
    Write-Output "Dropping Database and Server Logins for $usr - Completed "  
    Write-Output "If there are no logins displayed above then no logins were found or dropped!"    
    Write-Output "###########################################" 
}

Using PowerShell to find a database amongst hundreds

As you know, I love PowerShell!

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 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 server is that database on?

It isn’t always asked like that. Sometimes it is a developer asking where the database for their UAT/SAT/FAT/Dev environment is. Sometimes it is a manager who requires the information for some documentation or report. I wrote it because whilst I am good, I am not good enough to remember the server location for many hundreds of databases and also because I wanted to enable a new starter to be as self sufficient as possible

When I first wrote this I thought it would be a case of simply using $databaseName.Contains($DatabaseNameSearch) but this did not work for MiXed case searches or DatAbaSe names so I had to convert both the search and the database name to lower case first

image

I create an empty hash table and then populate it with the results

image

Set a results variable to the names from the hash table and count the number of records

image

and call it like this

image

Note that the search uses the contains method so no need for wildcards

Results come out like this

image

You can find the code here

#############################################################################################
#
# NAME: Find-Database.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
#
# COMMENTS: Load function for finding a database
# USAGE: Find-Database DBName
# ������������������������


Function Find-Database ([string]$Search) {

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    # Pull a list of servers from a local text file

    $servers = Get-Content 'sqlservers.txt'

    #Create an empty Hash Table
    $ht = @{}
    $b = 0

    #Convert Search to Lower Case
    $DatabaseNameSearch = $search.ToLower()  

				Write-Output "#################################"
				Write-Output "Searching for $DatabaseNameSearch "  
				Write-Output "#################################"  

                                 

    #loop through each server and check database name against input
                    
    foreach ($server in $servers) {

        if (Test-Connection $Server -Count 1 -Quiet) {
            $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
    
            foreach ($database in $srv.Databases) {
                $databaseName = $database.Name.ToLower()

                if ($databaseName.Contains($DatabaseNameSearch)) {

                    $DatabaseNameResult = $database.name
                    $Key = "$Server -- $DatabaseNameResult"
                    $ht.add($Key , $b)
                    $b = $b + 1
                }
            }        
        }
    }

    $Results = $ht.GetEnumerator() | Sort-Object Name|Select Name
    $Resultscount = $ht.Count

    if ($Resultscount -gt 0) {

        Write-Output "###############   I Found It!!  #################"
        foreach ($R in $Results) {
            Write-Output $R.Name 
        }
    }
    Else {
        Write-Output "############    I am really sorry. I cannot find"  $DatabaseNameSearch  "Anywhere  ##################### "
    }             
}

 

Checking Drive Sizes with PowerShell

I have developed a series of PowerShell functions over time which save me time and effort whilst still enabling me to provide a good service to my customers.  I call it my PowerShell Box of Tricks and this is another post in the series.

Todays question which I often get asked is How much space is free on the drive?

A question often asked by developers during development and by DBAs when looking at provisioning new databases so I use this simple function to return the drive sizes using a WMI call with PowerShell

I first write the date out to the console with the Server name as I found that useful to show how much space had been freed when archiving data. Then a WMI query and a bit of maths and output to the console. The /1GB converts the drive size to something meaningful and you can see how the PercentFree is calculated from the two values using “{0:P2}”

image

Call it like this

image

and here are the results from my Azure VM. (See My previous posts on how to create your own Azure VMs with PowerShell)

image

You can find the script below

#############################################################################################
#
# NAME: Show-DriveSizes.ps1
# AUTHOR: Rob Sewell http://sqldbawiththebeard.com
# DATE:22/07/2013
#
# COMMENTS: Load function for displaying drivesizes
# USAGE: Show-DriveSizes server1
# ������������������������


Function Show-DriveSizes ([string]$Server) {
    $Date = Get-Date
    Write-Host -foregroundcolor DarkBlue -backgroundcolor yellow "$Server - - $Date"
    #interogate wmi service and return disk information
    $disks = Get-WmiObject -Class Win32_logicaldisk -Filter "Drivetype=3" -ComputerName $Server
    $diskData = $disks | Select DeviceID, VolumeName , 
    # select size in Gbs as int and label it SizeGb
    @{Name = "SizeGB"; Expression = {$_.size / 1GB -as [int]}},
    # select freespace in Gbs  and label it FreeGb and two deciaml places
    @{Name = "FreeGB"; Expression = {"{0:N2}" -f ($_.Freespace / 1GB)}},
    # select freespace as percentage two deciaml places  and label it PercentFree 
    @{Name = "PercentFree"; Expression = {"{0:P2}" -f ($_.Freespace / $_.Size)}}
    $diskdata 
                                                  
}                                                      

SQL Server Operators and Notifications with Powershell – Strange Enumerate issue fixed by @napalmgram

Alerting of issues across the SQL Server estate is important and recently I needed to audit the operators and the notifications that they were receiving.

I created a SQL Server Object

2013-09-04_125056

One of the important things to remember when investigating SMO is the Get-Member cmdlet. This will show all methods and properties of the object

$server | Get-Member

gave me the JobServer Property

$Server.JobServer|gm

includes the Operator Property

$Server.JobServer.Operators | gm
2013-09-04_125717

has the EnumJobNotifications and EnumNotifications methods

So it was easy to loop through each server in the servers.txt file and enumerate the notifications for each Operator

2013-09-04_130052

and create a simple report

However this does not work as it does not perform the second enumerate. Try it yourself, switch round the EnumJobNotifications and EnumNotifications methods in that script and see what happens.

So I ended up with two functions

2013-09-04_174005
2013-09-04_173953

and I thought I could do this

2013-09-04_174056

But that doesnt work

So I tried this

2013-09-04_174112

and that doesnt work either

Now the reports are coming out showing the correct number of lines but not displaying them. I spent a period of time on my Azure boxes trying to work a way around this. I set the outputs to both enums to a variable and noted that they are different type of objects.

2013-09-05_113931

Job Notifications are System.Object and Alert Notifications are System.Array

I tried to enumerate through each member of the array and display them but got too tired to finish but I had contacted my friend Stuart Moore Twitter | Blog who had a look and resolved it by simply piping the Enumerates to Format-Table. Thank you Stuart.

So the final script is as follows

2013-09-05_114601

and the script is

#############################################################################################
#
# NAME: Show-SQLServerOperators.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:03/09/2013
#
# COMMENTS: Load function for Enumerating Operators and Notifications
# ————————————————————————

Function Show-SQLServerOperators ($SQLServer) {
    Write-Output "############### $SQLServer ##########################"
    Write-Output "#####################################################`n"     

    $server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
        
        
    foreach ($Operator in $server.JobServer.Operators) {
        $Operator = New-Object ("$SMO.Agent.Operator") ($server.JobServer, $Operator)

        $OpName = $Operator.Name
        Write-Output "Operator $OpName"
        Write-Output "`n###### Job Notifications   ######"
        $Operator.EnumJobNotifications()| Select JobName | Format-Table
        Write-Output "#####################################################`n"  
        Write-Output "`n###### Alert Notifications  #######"
        $Operator.EnumNotifications() | Select AlertName | Format-Table
        Write-Output "#####################################################`n"  
                 
    }
 
}        

SQL login object permissions via 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 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 Smile, 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.

image

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

image

To call it simply load the function

image

and a report

image

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
                    }
                }
            }
        }
    }
}

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

#############################################################################################
#
# NAME: Show-SQLUserPermissions.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:06/08/2013
#
# COMMENTS: Load function to Display the permissions a user has across the estate
# NOTE - Will not show permissions granted through AD Group Membership
# 
# USAGE Show-SQLUserPermissions DBAwithaBeard


Function Show-SQLUserPermissions ($user)
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Suppress Error messages - They will be displayed at the end

$ErrorActionPreference = "SilentlyContinue"
#cls
$Query = @"
SELECT 
IL.ServerName
FROM [dbo].[InstanceList] IL
WHERE NotContactable = 0
AND Inactive = 0
	AND DatabaseEngine = 'Microsoft SQL Server'
"@

Try
{
$Results = (Invoke-Sqlcmd -ServerInstance HMDBS02 -Database DBADatabase -Query $query -ErrorAction Stop).ServerName
}
catch
{
Write-Error "Unable to Connect to the DBADatabase - Please Check"
}
# Create an array for the username and each domain slash username

$logins = @("DOMAIN1\$user","DOMAIN3\$user", "DOMAIN4\$user" ,"$user" )
Write-Output "#################################" 
                Write-Output "Logins for `n $logins displayed below" 
                Write-Output "################################# `n" 

	#loop through each server and each database and display usernames, servers and databases
       Write-Output " Server Logins"
         foreach($server in $Results)
{
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
    
    		foreach($login in $logins)
		{
    
    			if($srv.Logins.Contains($login))
			{

                                    Write-Output "`n $server , $login " 
                                            foreach ($Role in $Srv.Roles)
                                {
                                    $RoleMembers = $Role.EnumServerRoleMembers()
                                    
                                        if($RoleMembers -contains $login)
                                        {
                                        Write-Output " $login is a member of $Role on $Server"
                                        }
                                }

			}
            
            else
            {

            }
         }
}
      Write-Output "`n#########################################"
     Write-Output "`n Database Logins"               
foreach($server in $servers)
{
	$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
    
	foreach($database in $srv.Databases)
	{
		foreach($login in $logins)
		{
			if($database.Users.Contains($login))
			{
                                       Write-Output "`n $server , $database , $login " 
                        foreach($role in $Database.Roles)
                                {
                                    $RoleMembers = $Role.EnumMembers()
                                    
                                        if($RoleMembers -contains $login)
                                        {
                                        Write-Output " $login is a member of $Role Role on $Database on $Server"
                                        }
                                }
                    

			}
                else
                    {
                        continue
                    }   
           
		}
	}
    }
   Write-Output "`n#########################################"
   Write-Output "Finished - If there are no logins displayed above then no logins were found!"    
   Write-Output "#########################################" 





}

 

Checking for SQL Server logins with PowerShell

As some of you may 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 database does this account have access to?

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. It is usually followed by what permissions do they have which is covered by my next blog post.

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

image

Then loop through each server and if the login exists write it out to the window.

image

I then repeat this but loop through each database as well

image

A little bit of formatting is added and then a quick easy report that can easily be copied to an email as required.

To call it simply load the function

image

and get the results

image

The code is below

<#
.Synopsis
   A workflow to display users server and database logins across a SQL estate
.DESCRIPTION
    Display a list of server login and database user and login for SQL servers listed 
 in sqlservers.txt file from a range of domains
    AUTHOR: Rob Sewell http://sqldbawithabeard.com
    LAST UPDATE: DATE:07/01/2015
.EXAMPLE
   Show-SQLUserLogins DBAwithaBeard

   Shows the SQL Server logins and database users matching DOMAIN1\DBAWithaBeard,DOMAIN2\DBAWithaBeard, DBAWithaBeard
#>

Workflow Show-UserLogins
{
    
param ([string]$usr)
$servers = Get-Content '\\hdat01\ICTS\ICTS\DBA-SAP\sql\Powershell Scripts\sqlservers.txt'
$ErrorActionPreference = "SilentlyContinue"

# Create an array for the username and each domain slash username

$logins = @("DOMAIN1\$usr","DOMAIN2\$usr", "DOMAIN3\$usr" ,"$usr" )

Write-Output "#################################" 
Write-Output "SQL Servers, Databases and Logins for `n$logins displayed below " 
Write-Output "################################# `n" 

#loop through each server and each database and display usernames, servers and databases
Write-Output " Server Logins`n"

foreach -parallel ($server in $servers)
    {
    inlinescript
        {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Using:server
        if(!$srv.Version)
            {
            Write-Output "$Using:server is not contactable - Please Check Manually"
            }
        else
            {              
            foreach ($login in $Using:logins)
                {      
                if($srv.Logins.Contains($login))
                    {
                    Write-Output " $Using:server -- $login " 
                    }           
                else
                    {
                    continue
                    }
                } 
            }         
        }
    }
Write-Output "`n###########################"
Write-Output "`n Database Logins`n"
foreach -parallel ($server in $servers)
    {
    inlinescript
        {
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Using:server
        if(!$srv.Version)
            {
            Write-Output "$Using:server is not contactable - Please Check Manually"
            }
        else
            {                                                                
            foreach($database in $srv.Databases|Where-Object{$_.IsAccessible -eq $True})
                {
                foreach($login in $Using:logins)
                    {
                    if($database.Users.Contains($login))
                        {
                        Write-Output " $Using:server -- $database -- $login " 
                        }
                    else
                        {
                        }   
                    }
                }
            }
        }
    }    
Write-Output "`n#########################################"
Write-Output "Finished - If there are no logins displayed above then no logins were found!"    
Write-Output "#########################################"  
}