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

Creating SQL Server Database with PowerShell

This morning I have been setting up my Azure Servers in preparation for my presentation to the Cardiff SQL User Group this month.

I used my scripts from My Post on Spinning Up Azure SQL Boxes to create two servers and then I wanted to create some databases

I decided it was time to write a Create-Database function using a number of scripts that I have used to create individual databases.

 

Errors

Whilst finalising the function I didn’t quite get it right sometimes and was faced with an error.

image

Not the most useful of errors to troubleshoot. The issue could be anywhere in the script

You can view the last errors PowerShell has shown using $Errors. This gives you the last 500 errors but you can see the last error by using $Error[0] if you pipe it to Format-List you can get a more detailed error message so I added a try catch to the function which gave me an error message I could resolve.

image

Much better. The problem was

Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\.LDF’ because it already exists.

Mistyping a variable has caused this. Creating an empty file name variable which then threw the error the second(and third,fourth fifth) times I ran the script but this error pointed me to it.

Creating Database

There are a vast number of variables you can set when creating a database. I decided to set File Sizes, File Growth Sizes, Max File Sizes and Recovery Model. I only set Server and Database Name as mandatory parameters and gave the other parameters default values

image

We take the parameters for file sizes in MB and set them to KB

image

Then set the default file locations. Create a database object, a Primary file group object and add the file group object to the database object

image

Add a User File Group for User objects

image

Create a database file on the primary file group using the variables set earlier

image

Do the same for the user file and then create a Log File

image

Set the Recovery Model and create the database and then set the user file group as the default

image

Finally catch the errors

image

It can then be called as follows Create-Database SERVERNAME DATABASENAME

image

or by setting all the parameters Create-Database -Server Fade2black -DBName DatabaseTest -SysFileSize 10 -UserFileSize 15 -LogFileSize 20 -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL

image

This means that I can easily and quickly set up several databases of different types and sizes

The script can be found here

#############################################################################################
#
# NAME: Create-Database.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:08/09/2013
#
# COMMENTS: Load function for creating a database
#           Only Server and DB Name are mandatory the rest will be set to small defaults
#
# USAGE:  Create-Database -Server Fade2black -DBName Test35 -SysFileSize 10 -UserFileSize 15 -LogFileSize 20
# -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL
# ————————————————————————


Function Create-Database {
    Param(
        [Parameter(Mandatory = $true)]
        [String]$Server ,
        [Parameter(Mandatory = $true)]
        [String]$DBName,
        [Parameter(Mandatory = $false)]
        [int]$SysFileSize = 5,
        [Parameter(Mandatory = $false)]
        [int]$UserFileSize = 25,
        [Parameter(Mandatory = $false)]
        [int]$LogFileSize = 25,
        [Parameter(Mandatory = $false)]
        [int]$UserFileGrowth = 5,
        [Parameter(Mandatory = $false)]
        [int]$UserFileMaxSize = 100,
        [Parameter(Mandatory = $false)]
        [int]$LogFileGrowth = 5,
        [Parameter(Mandatory = $false)]
        $LogFileMaxSize = 100,
        [Parameter(Mandatory = $false)]
        [String]$DBRecModel = 'FULL'
    )

    try {
        # Set server object
        $srv = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $server
        $DB = $srv.Databases[$DBName]
    
        # Define the variables
        # Set the file sizes (sizes are in KB, so multiply here to MB)
        $SysFileSize = [double]($SysFileSize * 1024.0)
        $UserFileSize = [double] ($UserFileSize * 1024.0)
        $LogFileSize = [double] ($LogFileSize * 1024.0)
        $UserFileGrowth = [double] ($UserFileGrowth * 1024.0)
        $UserFileMaxSize = [double] ($UserFileMaxSize * 1024.0)
        $LogFileGrowth = [double] ($LogFileGrowth * 1024.0)
        $LogFileMaxSize = [double] ($LogFileMaxSize * 1024.0)
   

        Write-Output "Creating database: $DBName"
 
        # Set the Default File Locations
        $DefaultDataLoc = $srv.Settings.DefaultFile
        $DefaultLogLoc = $srv.Settings.DefaultLog
 
        # If these are not set, then use the location of the master db mdf/ldf
        if ($DefaultDataLoc.Length -EQ 0) {$DefaultDataLoc = $srv.Information.MasterDBPath}
        if ($DefaultLogLoc.Length -EQ 0) {$DefaultLogLoc = $srv.Information.MasterDBLogPath}
 
        # new database object
        $DB = New-Object ('Microsoft.SqlServer.Management.SMO.Database') ($srv, $DBName)
 
        # new filegroup object
        $PrimaryFG = New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($DB, 'PRIMARY')
        # Add the filegroup object to the database object
        $DB.FileGroups.Add($PrimaryFG )
 
        # Best practice is to separate the system objects from the user objects.
        # So create a seperate User File Group
        $UserFG = New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($DB, 'UserFG')
        $DB.FileGroups.Add($UserFG)
 
        # Create the database files
        # First, create a data file on the primary filegroup.
        $SystemFileName = $DBName + "_System"
        $SysFile = New-Object ('Microsoft.SqlServer.Management.SMO.DataFile') ($PrimaryFG , $SystemFileName)
        $PrimaryFG.Files.Add($SysFile)
        $SysFile.FileName = $DefaultDataLoc + $SystemFileName + ".MDF"
        $SysFile.Size = $SysFileSize
        $SysFile.GrowthType = "None"
        $SysFile.IsPrimaryFile = 'True'
 
        # Now create the data file for the user objects
        $UserFileName = $DBName + "_User"
        $UserFile = New-Object ('Microsoft.SqlServer.Management.SMO.Datafile') ($UserFG, $UserFileName)
        $UserFG.Files.Add($UserFile)
        $UserFile.FileName = $DefaultDataLoc + $UserFileName + ".NDF"
        $UserFile.Size = $UserFileSize
        $UserFile.GrowthType = "KB"
        $UserFile.Growth = $UserFileGrowth
        $UserFile.MaxSize = $UserFileMaxSize
 
        # Create a log file for this database
        $LogFileName = $DBName + "_Log"
        $LogFile = New-Object ('Microsoft.SqlServer.Management.SMO.LogFile') ($DB, $LogFileName)
        $DB.LogFiles.Add($LogFile)
        $LogFile.FileName = $DefaultLogLoc + $LogFileName + ".LDF"
        $LogFile.Size = $LogFileSize
        $LogFile.GrowthType = "KB"
        $LogFile.Growth = $LogFileGrowth
        $LogFile.MaxSize = $LogFileMaxSize
 
        #Set the Recovery Model
        $DB.RecoveryModel = $DBRecModel
        #Create the database
        $DB.Create()
 
        #Make the user filegroup the default
        $UserFG = $DB.FileGroups['UserFG']
        $UserFG.IsDefault = $true
        $UserFG.Alter()
        $DB.Alter()

        Write-Output " $DBName Created"
        Write-Output "System File"
        $SysFile| Select Name, FileName, Size, MaxSize, GrowthType| Format-List
        Write-Output "User File"
        $UserFile| Select Name, FileName, Size, MaxSize, GrowthType, Growth| Format-List
        Write-Output "LogFile"
        $LogFile| Select Name, FileName, Size, MaxSize, GrowthType, Growth| Format-List
        Write-Output "Recovery Model"
        $DB.RecoveryModel

    }
    Catch {
        $error[0] | fl * -force
    }
}

Alter SQL Mirroring Endpoint Owner with Powershell

Whilst using my Drop-SQLLogins function, which is one of my PowerShell Box Of Tricks series, it failed to delete logins on some servers with the error

Login domain\user’ has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)

I used the Show-SQLPermissions function and added the .grantor property to try and locate the permission the account had granted but it came back blank. A bit of googling and a AHA moment and I remembered mirroring

I checked the mirroring endpoints

mirroring endpoitn check

and found the endpoints with the user as the owner so I needed to change them

This can be done in T-SQL as follows

alter endpoint

but to do it on many endpoints it is easier to do it with Powershell

alterendpointPS

I could then drop the user successfully

$svrs = ## list of servers Get-Content from text fiel etc

foreach ($svr in $svrs) {
    $server = New-Object Microsoft.SQLServer.Management.Smo.Server $svrs
    foreach ($endpoint in $server.Endpoints['Mirroring']) {
        if ($endpoint.Owner = 'Domain\User') {
            $endpoint.Owner = 'Domain\NEWUser'
            $endpoint.Alter()
        }        
    }
}

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 "#########################################" 





}

 

SQL Express Migration Auto Close Setting

With over 700 databases to look after at MyWork automation is high on my list of priorities. I have two PowerShell scripts which run regularly checking SQL Error logs. One checks for the output from DBCC CHECKDB and one for errors. They then email the results to the DBA team.

This week we noticed that a new database was creating a lot of entries. It appeared to be starting up every few minutes. A bit of investigation by my colleague revealed that this database had been created on SQL Express and migrated to SQL Server.

SQL Express sets AUTO_CLOSE to on by default and this is what was creating the entries.

What does the AUTO_CLOSE setting do?

According to BoL Link

DescriptionDefault value
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

 

When set to OFF, the database remains open after the last user exits.

True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, and False for all other editions, regardless of operating system.

That explains what was happening, the database was shutting down as the session finished and then starting back up again when the next one started. Repeatedly. Filling up the log files with entries, resetting the DMVs and using resources unnecessarily.

To find databases with this setting on query the master.sys.databases for the is_auto_close_on column Link or check the properties page in SSMS

image

You can change the setting there or with T-SQL

Of course I like to do it with PowerShell!!

To find the databases with AUTO_CLOSE setting on

To change the setting with PowerShell

$svrs = ## list of servers Get-Content from text fiel etc

foreach ($svr in $svrs) {
    $server = New-Object Microsoft.SQLServer.Management.Smo.Server $svrs
    foreach ($db in $server.Databases) {
        if ($db.AutoClose = $true) {
            Write-Output "$Server - $($DB.Name) AutoClose ON"
        }        
    }
    
}

$Svr = 'SERVERNAME'
$DB = 'DatabaseName'
$server = New-Object Microsoft.SQLServer.Management.Smo.Server $svrs
$db.AutoClose = $false
$db.Alter()