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
August 18, 2021 at 3:10 pm
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.
A little bit of PowerShell and you can script out all you're looking for.
August 25, 2021 at 3:53 pm
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
August 25, 2021 at 5:14 pm
You can do something like this with dbatools.
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
Change is inevitable... Change for the better is not.
August 25, 2021 at 6:21 pm
Tom Uellner wrote:You can do something like this with dbatools.
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"
August 25, 2021 at 6:24 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply