Complete permissions script

  • Hi Everyone

    Is there a script that can output a FULL audit of my SQL instance. As in script out:

    Logins

    Server roles

    Server securables

    Database Users

    Database Roles

    Database securables

     

    I have bits and pieces of this but cannot find a complete solution.

     

    Any help is appreciated

     

    Thanks

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • You can do something like this with dbatools.

    http://www.dbatools.io

    A little bit of PowerShell and you can script out all you're looking for.

  • Thanks

    Yes, I use DBATools for most of my day to day things as it is.

    I was just looking for a complete T-SQL script.

    Thanks

  • Tom Uellner wrote:

    You can do something like this with dbatools.

    http://www.dbatools.io

    A little bit of PowerShell and you can script out all you're looking for.

    Since there are a whole lot of individual tools to pick from and a whole lot of them don't have helpful names, which ones are you specifically talking about?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Tom Uellner wrote:

    You can do something like this with dbatools.

    http://www.dbatools.io

    A little bit of PowerShell and you can script out all you're looking for.

    Since there are a whole lot of individual tools to pick from and a whole lot of them don't have helpful names, which ones are you specifically talking about?

    Very true. There are a bunch and it took me some wading through to figure out what I wanted to use. Rather than just list the modules I used, I am just going to include my script (server, directory and email names changed to protect the guilty 🙂 )

    I'm no PowerShell pro so I'm sure things could be done more eloquently but here it is. Hopefully someone will find this useful:

    <#
    Author: Tom Uellner
    Date: 2019-11-21
    Descr: Save the details of our SQL Server configurations to sub-folders under a specified root so
    we can recreate them as quickly as possible in case of a DR event. When all the specified
    SQL Servers information has been saved, move the resulting folder structure to an off-site
    location in case the DR event also take down our local SAN.
    Most of the heavy lifting in the script is done by dbatools.
    #>

    $StartTime = Get-Date
    Write-Host "Start export of SQL Server configuration:" $StartTime
    Write-Host ""

    $Step = ""

    # List of servers we're documenting
    $Servers = 'Server001', 'Server002', 'Server003'

    # Where are we saving the data and what is the current date time to add to the filename?
    $SaveRootPath = '\\BACKUP001\Data\SQLServerConfigs\'
    $DateTime = Get-Date -Format 'yyyyMMdd_HHmmss'
    CD $SaveRootPath

    function Script:Get-ReadMe
    {
    Write-Output "SQL Server State Information and Scripts"
    Write-Output ""
    Write-Output "This directory contains information on OS configuration and SQL Server configuration"
    Write-Output "and settings. It also contains scripts to configure a new install of SQL Server to"
    Write-Output "match the current SQL Server. This is meant to save as much time as possible during"
    Write-Output "a DR event when spinning up a new environment and SQL Server."
    Write-Output ""
    Write-Output ""
    Write-Output "Readme last updated: 2019-11-15"
    }

    # Loop through all the servers and generate the scripts and documentation
    foreach($Server in $Servers)
    {

    try
    {
    $Step = "Starting scripting of SQL Server: " + $Server

    # Create a subdirectory for the SQL Server if it does not exist
    $SavePath = ''
    if(-not (Test-Path -Path $SaveRootPath$Server))
    {
    $Step = "Creating directory: " + $SaveRootPath
    New-Item -ItemType "Directory" -Path $SaveRootPath$Server
    }
    $SavePath = $SaveRootPath + $Server + '\'


    # Create the PreviousRun folder if it does not exist
    $PreviousPath = ''
    if(-not (Test-Path -Path $SavePath'PreviousRun\'))
    {
    $Step = "Creating directory: " + $SaveRootPath + "PreviousRun\"
    New-Item -ItemType "Directory" -Path $SavePath'PreviousRun\'
    }
    $PreviousPath = $SavePath + 'PreviousRun\'

    CD $SavePath

    # Delete all the files from the \PreviousRun folder
    Write-Host ""
    Write-Host "Deleting files from" $PreviousPath
    $Step = "Deleting files from directory: " + $PreviousPath
    Remove-Item $PreviousPath'*.*'

    # Move all the current files to the \PreviousRun folder
    Write-Host "Moving files from last run to" $PreviousPath
    $Step = "Moving file from: " + $SavePath + " to " + $PreviousPath
    Move-Item -Path $SavePath'*.*' -Destination $PreviousPath

    # Delete all subfolders except for the PreviousRun folder
    $Step = "Cleanup old items except for the PreviousRun directory in: " + $SavePath
    Get-ChildItem -Path $SavePath -Include * -Exclude "PreviousRun" | Remove-Item -Recurse


    Write-Host "Starting export of" $Server "to" $SavePath
    Write-Host ""

    $Step = "Writing configuration files"
    Get-ReadMe | Out-File -FilePath $SavePath$Server-00ReadMe-$DateTime.txt
    Get-DbaComputerSystem -ComputerName $Server | Out-File -FilePath $SavePath$Server-ComputerSystem-$DateTime.txt
    Get-DbaOperatingSystem -ComputerName $Server | Out-File -FilePath $SavePath$Server-OperatingSystem-$DateTime.txt
    Get-DbaDiskSpace -ComputerName $Server | Out-File -FilePath $SavePath$Server-DiskSpace-$DateTime.txt
    Get-DbaMaxMemory -SqlInstance $Server | Out-File -FilePath $SavePath$Server-Memory-$DateTime.txt
    Invoke-DbaQuery -SqlInstance $Server -Database master -Query "SELECT @@VERSION AS SQLServerVersion;" | Format-Table -AutoSize -Wrap | Out-File -FilePath $SavePath$Server-SQLServerVersion-$DateTime.txt
    Export-DbaInstance -SqlInstance $Server -Path $SavePath -IncludeDbMasterKey

    # Export-DbaInstance creates a timestamped folder under the specified path. Move the files from that path
    # to the path we have already defined to keep all the files in one folder.
    $Step = "Moving Export-DbaInstance files from subdirectory to current directory"
    $Folders = Get-ChildItem -Path $SavePath -Directory -Recurse -Exclude "PreviousRun"
    Move-Item -Path $Folders"\*.*" -Destination $SavePath

    # Now remove the folder created by Export-DbaInstance
    $Step = "Removing the subdirectory created by Export-DbaInstance"
    Remove-Item -Path $Folders

    Write-Host "Finished export of" $Server
    Write-Host ""
    }
    catch
    {
    $ErrorText = "The following error occurred while scripting the SQL Serversrnrn" + $PSItem.ToString() + "rnrn The current step was: " + $Step + "rn"

    Write-Host $ErrorText -ForegroundColor Red

    # Email the error (Change To email address to Dba group after testing is completed)
    Send-MailMessage -From "dba@mycompany.com" -To "monitor@mycompany.com" -Subject "Error creating SQL Server DR Scripts" -Body $ErrorText -SmtpServer "smtp.mycompany.com" -Port 25
    }
    }

    $EndTime = Get-Date
    Write-Host ""
    Write-Host "Finished export of SQL Server configuration:" $EndTime
    Write-Host "Runtime:" ($EndTime - $StartTime).TotalSeconds "seconds"

  • Get-DbaPermission and Get-DbaUserPermission would be the starting point for a permissions extract, is uses the DISA STIG script so people find that a handy one if they need to comply with that.

    Depending on requirements will depend on the right function.

    Going off the OP list

    Logins - Get-DbaLogin / Export-DbaLogin

    Users - Get-DbaDbUser / Export-DbaUser

    DB Roles - Get-DbaDbRole / Export-DbaDbRole

    DB Role Members - Get-DbaDbRoleMember

    Server Roles - Get-DbaServerRole / Export-DbaServerRole

    Server Role Members - Get-DbaServerRoleMember

    Securables are the two permission functions mentions

    Some of those also have an Export version so save to script, those that don’t you can easily pipe the Get to Export-DbaScript to save to a SQL file.

     

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply