I have made another modification, adding the “cd c: #Required when referencing UNC path” line near the beginning. This gets around an odd issue whenever the path you are deleting from is a UNC path. HERE is the link to the updated post and scripts.
Another update to my “Robust Posh Script to Delete Files” Script Leave a comment
Update to my “Robust Posh Script to Delete Files” Script Leave a comment
I have made a modification to my “Robust Posh Script to Delete Files” scripts so that they will only delete actual files in the specified path, and not folders. It does this by using the statement “if ((Test-Path $_.fullname -pathtype leaf)” to check whether the object being considered is a file. HERE is the link to the updated post and scripts.
Robust Posh Script to Delete Files, Checks for Locked Files First Leave a comment
UPDATE 1/11/2019: I have made another modification, adding the “cd c: #Required when referencing UNC path” line near the beginning. This gets around an odd issue whenever the path you are deleting from is a UNC path.
UPDATE 2/2/2016: I have made a modification to the scripts so that they will only delete actual files in the specified path, and not folders. It does this by using the statement “if ((Test-Path $_.fullname -pathtype leaf)” to check whether the object being considered is a file.
I am back blogging for the New Year 2016! Happy New Year, everyone!
Before we begin, here are source files for the below scripts:
PowerShell Script
SQL Server Agent Script (incorporating the PowerShell Script)
Here is a nice Posh (PowerShell) script I have written to delete from the specified directory files that are older than the number of days specified. The script is robust in the following ways:
- It checks if a file that is a candidate to delete is locked (and therefore cannot be deleted). If it finds those files, output is generated to let you know the number of files that could not be deleted and lists the names of up to the first 10 files.
Here is an example of the output (formatted for readability):
The error information returned by PowerShell is: Error: A total of 1 File(s) Could Not Be Deleted. See the following output: Error: The Locked File "MyBackupFile.BKP" could not be deleted. Note: 0 additional locked files could not be deleted. Total # Files Older Than (34) Days Successfully Deleted From Directory "B:\Backups\myBackups\": 1458.
- If all deletions are successful, the output looks similar to the following:
Total # Files Older Than (34) Days Successfully Deleted From Directory "B:\Backups\myBackups\": 1458.
- The script can be used to delete from local directories or network shares.
- I have designed the script to be run as stand-alone Posh script, or as a SQL Server Agent job, which is scheduled to run on a regular basis. I have successfully tested the SS Agent job on all versions of SQL Server from SQL 2008 and above (up through SQL 2014).
- Output from the PowerShell script is appended to the SQL Server Agent job’s output history, for your later examination.
Note: For the SQL Server Agent job to work, the Windows service account that the SQL Server Agent runs under must have delete rights in the specified directory.
Here is the stand-alone PowerShell script, and an example of a scripted-out SQL Server Agent job.
The only edits to the scripts you may need to make are as follows:
PowerShell script:
#Set the following to the days to keep value #and the directory value $daysToKeep = 34 $directory = 'B:\Backups\myBackups\'
SQL Agent Job script:
SET @BkupDir = 'B:\Backups\myBackups\' --include trailing backslash #Set the following to the days to keep value #and the directory value $daysToKeep = 34
PowerShell script:
#Set the following to the days to keep value
#and the directory value
$daysToKeep = 34
$directory = 'B:\Backups\myBackups\'
cd c: #Required when referencing UNC path
if (Test-Path $directory)
{
$olderThanDt = (Get-Date).AddDays(-$daysToKeep)
$cntDel = 0
$cntLocked = 0
$strMsgFilesRemovedBase = "Total # Files Older Than ({0}) Days Successfully Deleted From Directory `"$directory`"`:" -f $daysToKeep
$strLockedList = ""
gci $directory | foreach {
$curFullName = $_.Name
$curLastWrite = $_.LastWriteTime
if ((Test-Path $_.fullname -pathtype leaf) -and ($curLastWrite -lt $olderThanDt))
{
try
{ [IO.File]::OpenWrite($_.FullName).close();
if ($curLastWrite -lt $olderThanDt)
{
$cntDel += 1
Remove-Item $_.fullname
}
}
catch
{
$cntLocked += 1
if ($cntLocked -lt 11)
{
$strLockedList += "`nError: The Locked File `"$curFullName`" could not be deleted."}
}
}
}
if ($cntLocked -gt 0)
{
$cntLockedAddl = $cntLocked - 10
if ($cntLockedAddl -lt 0)
{
$cntLockedAddl = 0
}
$strLockedList += "`nNote: $cntLockedAddl additional locked files could not be deleted."
}
if ($cntDel -gt 0)
{write-output "$strMsgFilesRemovedBase $cntDel."}
else
{write-output "$strMsgFilesRemovedBase None Found."}
if ($cntLocked -gt 0)
{
throw "Error: A total of $cntLocked File(s) Could Not Be Deleted. See the following output: $strLockedList `n$strMsgFilesRemovedBase $cntDel."
}
}
else
{throw "Error: Specified directory could not be accessed or does not exist."}
SQL Agent Job script:
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @BkupDir NVARCHAR(1000), @cmd NVARCHAR(MAX)
SET @BkupDir = 'B:\Backups\myBackups\' --include trailing backslash
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
SET @cmd = N'#Set the following to the days to keep value
#and the directory value
$daysToKeep = 34
$directory = ''' + @BkupDir + N'''
cd c: #Required when referencing UNC path
if (Test-Path $directory)
{
$olderThanDt = (Get-Date).AddDays(-$daysToKeep)
$cntDel = 0
$cntLocked = 0
$strMsgFilesRemovedBase = "Total # Files Older Than ({0}) Days Successfully Deleted From Directory `"$directory`"`:" -f $daysToKeep
$strLockedList = ""
gci $directory | foreach {
$curFullName = $_.Name
$curLastWrite = $_.LastWriteTime
if ((Test-Path $_.fullname -pathtype leaf) -and ($curLastWrite -lt $olderThanDt))
{
try
{ [IO.File]::OpenWrite($_.FullName).close();
if ($curLastWrite -lt $olderThanDt)
{
$cntDel += 1
Remove-Item $_.fullname
}
}
catch
{
$cntLocked += 1
if ($cntLocked -lt 11)
{
$strLockedList += "`nError: The Locked File `"$curFullName`" could not be deleted."}
}
}
}
if ($cntLocked -gt 0)
{
$cntLockedAddl = $cntLocked - 10
if ($cntLockedAddl -lt 0)
{
$cntLockedAddl = 0
}
$strLockedList += "`nNote: $cntLockedAddl additional locked files could not be deleted."
}
if ($cntDel -gt 0)
{write-output "$strMsgFilesRemovedBase $cntDel."}
else
{write-output "$strMsgFilesRemovedBase None Found."}
if ($cntLocked -gt 0)
{
throw "Error: A total of $cntLocked File(s) Could Not Be Deleted. See the following output: $strLockedList `n$strMsgFilesRemovedBase $cntDel."
}
}
else
{throw "Error: Specified directory could not be accessed or does not exist."}'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Posh - Clean Up Old Backup Files',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
/****** Object: Step [Delete old files] Script Date: 10/13/2015 8:18:11 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete old files',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=@cmd,
@database_name=N'master',
@flags=32
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1',
@enabled=1,
@freq_type=8,
@freq_interval=32,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20151008,
@active_end_date=99991231,
@active_start_time=233000,
@active_end_time=235959
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Enjoy!
Query to Display Recent Data and TLog File Autogrowth and Autoshrink Events 2 comments
Here is a query to read the default trace on your SQL instance and produce a report of recent Data and TLog file autogrowth and autoshrink events that have occurred for that SQL instance. The query works for SQL 2005 and above, and there are no edits required.
The query is a modified version of the one used by the SQL 2008 R2 Management Studio’s “Standard Report” named “Disk Usage,” which has a section that displays this data (Object Explorer –> right click on database –> Reports –> Standard Reports –> “Disk Usage”).
Just open a query window connected to the SQL instance you wish to report on and run the query. This version displays data for all databases, including the database name, the date of the autogrowth event, the number of milliseconds it took, and the size of the growth in MB.
Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.
--NE - 6/17/2011 --Query to display recent Data and TLog file autogrowth and autoshrink events --for all databases on the instance. Based on query used by the SSMS Standard --Report named "Disk Usage" which has a section that displays this data. Data --is retrieved from the default trace. --Works for SQL 2005 and above. USE [master] GO BEGIN TRY IF (SELECT CONVERT(INT,value_in_use) FROM sys.configurations WHERE NAME = 'default trace enabled') = 1 BEGIN DECLARE @curr_tracefilename VARCHAR(500); DECLARE @base_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SELECT @indx = PATINDEX('%\%', @curr_tracefilename) ; SET @curr_tracefilename = REVERSE(@curr_tracefilename) ; SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; SELECT --(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1, ServerName AS [SQL_Instance], --CONVERT(INT, EventClass) AS EventClass, DatabaseName AS [Database_Name], Filename AS [Logical_File_Name], (Duration/1000) AS [Duration_MS], CONVERT(VARCHAR(50),StartTime, 100) AS [Start_Time], --EndTime, CAST((IntegerData*8.0/1024) AS DECIMAL(19,2)) AS [Change_In_Size_MB] FROM ::fn_trace_gettable(@base_tracefilename, default) WHERE EventClass >= 92 AND EventClass <= 95 --AND ServerName = @@SERVERNAME --AND DatabaseName = 'myDBName' ORDER BY DatabaseName, StartTime DESC; END ELSE SELECT -1 AS l1, 0 AS EventClass, 0 DatabaseName, 0 AS Filename, 0 AS Duration, 0 AS StartTime, 0 AS EndTime, 0 AS ChangeInSize END TRY BEGIN CATCH SELECT -100 AS l1, ERROR_NUMBER() AS EventClass, ERROR_SEVERITY() DatabaseName, ERROR_STATE() AS Filename, ERROR_MESSAGE() AS Duration, 1 AS StartTime, 1 AS EndTime, 1 AS ChangeInSize END CATCHPosted June 21, 2011 by Norm Enger in Microsoft SQL Server
Publishing Source Code to WordPress Using Windows Live Writer 2011 2 comments
My previous blog post “Publishing Code Snippets on My WordPress Blog” explained how I used a read-only textarea to publish source code. Alas, WordPress has started stripping out <textarea> tags when you publish to their site or edit existing posts. They explain that this is for security reasons at http://en.support.wordpress.com/code/.
The above WordPress article also references another article at http://en.support.wordpress.com/code/posting-source-code/ which explains that they have a “sourcecode” “short code” available which can help with publishing source code blocks or code snippets. Unfortunately, that article is muddled in how it presents how to insert the short codes, and also it does not offer any help for Windows Live Writer 2011 users.
After experimenting a bit, I have discovered how to easily use the short codes from within Live Writer, in combination with the <PRE> tag, and what to expect when using it in this editor.
The basic way to include a code block is as follows. Note that the short code tags are enclosed in square brackets rather than angle brackets. Also you will have to type this code in HTML view.
Please note that when you go back to the Edit view in Live Writer, the above “sourcecode” lines will appear as text. Do not edit these out. When you publish, the WordPress site will accept these and will not display them, but will interpret them correctly and display your code with the fancy highlighting.
A second important note: when in the Edit view in Live Writer, you will see your snippits appearing as plain text, without the syntax coloring. Don’t try to correct this. Once you publish the post to WordPress, it will appear as expected, with the syntax highlighting, toolbar, etc.
Please note that you must also include the <PRE> tag because if you do not, WordPress will remove the line breaks and tabs from your snippets when you publish.
In my example, the language=”sql” parameter specifies which language I want syntax highlighting for, in my case, SQL. The wraplines=”false” parameter ensures that a horizontal scroll bar will appear if I have longer lines of code. This particular option was needed with my WordPress theme, but you may need to experiment with this. The gutter=”false” parameter prevents line numbering in the code block. The full explanation of these and other parameters is explained in greater detail at http://en.support.wordpress.com/code/posting-source-code/.
Happy blogging!
Display Current Backup/Restore Progress Using DMVs 1 comment
Here is a script gleaned from an article at http://www.mssqltips.com/tip.asp?tip=2343, with some minor modifications of mine, which very nicely allows you to monitor SQL backup or restore progress, including estimated completion times, based on data from system DMVs. This script works for SQL 2005 and above.
What is nice also is that it not only reports on native SQL backups and restores, but also third-party backups and restores performed by tools such as Quest SQL LiteSpeed or the Simpana CommVault SQL backup agent.
Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.
--Display current backup/restore progress using DMVs. --SQL 2005 and above --From http://www.mssqltips.com/tip.asp?tip=2343 USE master SELECT session_id as SPID, CONVERT(VARCHAR(50),start_time,100) AS start_time, percent_complete, CONVERT(VARCHAR(50),dateadd(second,estimated_completion_time/1000, getdate()),100) as estimated_completion_time, command, a.text AS Query FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'Posted June 20, 2011 by Norm Enger in Microsoft SQL Server
Using Excel 2007 and Excel 2010 to Analyze Perfmon Captures in the Form of .csv Comma-Delimited Counter Logs 10 comments
Here is an explanation of how to easily use Excel 2007 and Excel 2010 to analyze performance captures saved to counter logs via Windows’ built-in perfmon utility. This article assumes that you have already collected whatever SQL or Windows or other performance counters you wish to analyze, and that you have captured them to counter logs in the form of one or more comma-delimited .csv files.
We will look at how to easily open a .csv file, apply a few formatting changes, and use the very handy Pivot Chart functionality built into Excel to graph your counters. Finally, we will demonstrate how to use date filters to drill deeper into specific time ranges, in order to view the graph data in greater detail.
The screen shots and steps below are for Excel 2007, but they should also work for Excel 2010.
Note that in the process below, Excel may not by default select the line chart type you prefer, for example one with data points displayed. If this is the case, right-click on an open area of the Pivot Table chart and select “Change Chart Type.” Select the left-most line chart type (the most basic line chart), and click the option at the bottom to make that your default type. See Excel help for more info.
Let’s get started.
1. Double-click on the .csv counter log file. The .csv extension should already be associated, so the file will automatically open in Excel.
2. Make some formatting changes.
a. Change the text in the A1 cell from “(PDH-CSV 4.0…” to simply “DateTime.”
b. Remove row 2 by right clicking on the row number –> “Delete.” This row, containing the first row of data, is typically a junk row.
c. Highlight the entire “A” column by clicking on the column header, then right-click anywhere in the column –> Format Cells…
d. On the Number tab, select the “Date” category and the “3/14/01 1:30 PM” type, and click OK.
e. Press Ctrl + Home to get the focus back on the A1 cell.
f. These steps are repetitive for every .csv file you will analyze, so I recommend that you automate steps a. through e. using an Excel macro, by having Excel record your actions, and then you will assign a keyboard shortcut to your macro. Please refer to the Excel help for how to do this.
Note that I have found using the Excel macro functionality for steps beyond this step unworkable.
3. Create your Pivot Chart
a. Switch to the “Insert” ribbon, click on the down-arrow on the “Pivot Table” button, and select “Pivot Chart.”
b. The Pivot Chart dialog “auto-magically” selects the correct Table/Range of data to analyze. Very nice functionality! Click OK.
c. In the PivotTable Field List box, click and drag “DateTime” down to the “Axis Fields (Categories)” pane below.
d. In this example, we are going to first analyze the total processor time. In the PivotTable Field List box, scroll down and find the “Processor(_Total)\% Processor Time” counter, and click and drag it to the “Values” pane below.
e. Notice that in this instance, Excel incorrectly guessed that I want to summarize the data by “Count of….” What I actually want is for Excel to summarize the data by “Sum of….” Sometimes Excel correctly guesses this, sometimes not.
To correct this, right-click on the the Excel header cell labeled “Count of \\mySQLInstance\Processor(_Total)\% Processor Time” –> “Summarize Data By” –> and select “Sum.”
You may find this step necessary for other counters that you want to graph later as well.
4. Format and size the graph to your liking.
a. You will now have a basic graph that looks something like this.
b. For this chart, I will change the Max value shown on the left axis from “120” to “100.” Right-click on the left axis area –> Format Axis. Change the “Maximum” option under “Axis Options” from “Auto” to “Fixed” and type in “100.” Then click Close.
c. Edit the “Title” area text, which currently reads “Total,” to your liking. I will insert the text “\\mySQLInstance\Processor(_Total)\% Processor Time”
d. I will also click on the legend box on the right and delete it.
e. Here is what my final graph will look like.
f. You can click on any open space within the chart to select the whole chart, then press Ctrl + C to copy, then paste the chart image into an email or Paint or your favorite editor.
g. Once you have saved the image of the chart, in the PivotTable Field List box, you can uncheck the “Processor(_Total)\% Processor Time” counter, then drag a different counter down into the Values pane.
h. You may repeat the process for as many different counters as you may wish to graph and analyze, reusing the same chart area.
5. Drilling deeper into specific time ranges.
a. Looking at the “Processor(_Total)\% Processor Time” chart I already created, I notice there was a CPU spike around the hour of 5:30 a.m. to 6:30 a.m. I want to investigate that time period more closely, and “zoom” the graph in, to that time period, so I can see in more detail what was happening during that hour.
b. In the PivotChart Filter Pane, click on the down-arrow next to “DateTime.”
c. Go to “Date Filters” –> and select “Between….”
d. Type in the beginning and ending dates/times in the format “m/d/yy h:mm am/pm” and click OK.
e. Your chart will now be “zoomed in” to the hour of 5:30 a.m. to 6:30 a.m. and will look something like this.
There are other features you may want to experiment with, such as adding trend lines, and making other changes to the display of the left axis area.
Happy charting!
Posted May 30, 2011 by Norm Enger in Excel, Microsoft SQL Server, Performance Monitoring
A PowerShell Script to Help You in Cleaning Up Old or Unused Files in Your SQL Data File Directories Leave a comment
Here is a PowerShell script to list non-locked files in SQL Data directories as possible candidates to delete, in order to free up space. This can be useful for identifying old files that, for whatever reason, were not deleted and may be wasting disk space.
Note: before deleting any file, make sure it is not needed. For example, if a database is offline, its data files will be closed (not locked) and thus show up on this report, but you may not want to delete the files in case someone should need to bring the database back online again.
There are two modifiable sections in the script that allow you to prevent files from being evaluated based on either the file extension or the file name.
The script works by simply looping through files in the directory that you pass in as a parameter, and attempts to open the first line of each file. If a file is locked (as in a live SQL data or transaction log file), the attempt to open it and read the first line will fail, and the file will not be reported on. If the attempt to open and read the first line is successful, then the file will be reported on as a candidate for deletion.
Being the paranoid, DBA-type, I have written the script so that it will not perform the actual deletion of any files. Again I recommend that a human being look at each listed file and make the final decision of whether to delete.
I like to run the script from a PowerShell 2.0 ISE window. The example execution in the script assumes you save this script to your local system as “C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1.” Please follow the directions within the comments of the script for a sample execution.
Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.
param([string]$theDir="") #Norm Enger - 4/30/2011 - PowerShell script to list non-locked files in SQL Data directories #as possible candidates to delete, in order to free up space. Note: before deleting any #file, make sure it is not needed. For example, if a database is offline, its data file #will be closed (not locked) and thus show up on this report, but you would not want to delete #the file in case someone should need to bring the database back online again. #To run, open a Powershell window and run a command like the following (starting with #the amperstand "&" character)... #& "C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1" -theDir "C:\mySQLDirectory\Data" #Output will be similar to the following: #C:\mySQLDirectory\Data\New Text Document.txt (10/8/2010 4:46:33 PM) #Array of extensions to exclude from report (change as needed)... $arrExcludeExtensions = ".preg",".old",".cer" #Array of file names to exclude from report (change as needed)... $arrExcludeFileNames = "distmdl.mdf","distmdl.ldf", ` "mssqlsystemresource.mdf","mssqlsystemresource.ldf" #Sets the folder location Set-Location $theDir foreach($file in get-childitem | Sort-Object $_.Name ` | Where-Object {$arrExcludeExtensions -notcontains $_.Extension ` -and $arrExcludeFileNames -notcontains $_.Name}) { #Get-Content returns error if the file is in use/locked #We limit lines to try to read at 1 using TotalCount... $content = Get-Content $file -TotalCount 1 -ErrorAction silentlycontinue if($?){ $file.FullName.ToString() + ` " (" + $file.LastWriteTime.ToString() + ")" } else{} #write out nothing }Posted April 30, 2011 by Norm Enger in Microsoft SQL Server, Powershell
An Exceptions-Based Report for SQL Backups – Part 2 Leave a comment
As promised, here is part 2 of of the blog “An Exceptions-Based Report for SQL Backups” (click HERE for the first blog).
Using the attached “Backups Exception Report.rdl” file as an example (click HERE to download), you can create a nifty report based on the stored procedure in the first post. Click HERE to see an example in .pdf format of what the report looks like when rendered. The report .RDL file is in SSRS 2005 format, so you can easily use it with SSRS 2005 and above.
One of the most useful – well fantastic, really! – features of this report and the accompanying stored procedure is that records are shown only for SQL backups that are missing. Potential exceptions are denoted in red and bolded. If there are no missing backups for the SQL instance, a message is displayed stating, “No exceptions were noted for this server.” In this way you have a quick and reliable way to identify when your backups are being missed, without having to wade through the vast majority of backups that are not missing.
You will also notice that the report displays records for databases that are in a status other than “ONLINE” (e.g. “OFFLINE” status), with the last backups of each type displayed (assuming one of the last backups ages over the number of prescribed days). This will be a handy visual reminder of any databases in an unusual status.
To use the example .RDL file, load it into an SSRS project in BIDS and open it. When it first opens, there may be an error displayed. Click to ignore it as it is just trying to connect the data source to a dummy SQL server name which I have embedded in the report. Also make sure you deploy the stored procedure from the first post to the master database of the instance you will be running this against.
Once the report is open in BIDS, go to the data sources tab and click to edit the data set. Within that dialog box, click to edit the data source and change the name of it, and the connection properties, to reflect the name of your SQL instance, then click OK. Then make sure the query type is set to Stored Procedure and in the query text field, paste the name of the procedure, rpt_SQL_Backups_Report. Click on the parameters tab and make sure there is a parameter named “@type” (don’t include the double quotes), and in the value column for that, make sure it says:
=”all_exceptions”
…(include the double quotes)
When you click OK, another error may be generated stating it cannot connect to a #TEMP table. This is expected. Click OK, then click the “Refresh Fields” button on the toolbar, and when it prompts you to enter a value for “@type,” enter the string “all_exceptions” (DON’T include the double quotes).
If you made a change to the name of the data set, you will need to point the table on the layout tab to use the new data set name. That is all! You are ready to start using this report.
Another enhancement, which I have not demonstrated here, is to include data sets for multiple SQL instances on the same report, and wire them up to separate tables on the layout tab (copying and pasting from the first table to easily create clones).
Happy reporting!
Posted March 25, 2011 by Norm Enger in Microsoft SQL Server
An Exceptions-Based Report for SQL Backups Leave a comment
UPDATE 3/26/2011: If you used an earlier version of the stored procedure, I have made a couple of minor corrections to the procedure below, including adding logic for including, in the all_exceptions implementation, dbs in the BULK_LOGGED recovery model, which were omitted before.
Here is a stored procedure that reports on SQL backups that are missing, based on the number of days since the last backups of each type, for each SQL database on an instance. This stored procedure works with all versions of SQL Server, from SQL 2000 and above.
In particular, calling the procedure with the “all_exceptions” parameter will show records for any databases in the SQL instance that have not had a Transaction Log backup in over 1 day (if in the FULL or BULK_LOGGED recovery models), or have not had a Full backup in over 7 days, or have not had a Differential backup in over 1 day. If there has never been a backup, this is indicated in the results by returning “999” in the “Days_Since_Last_Full,” “Days_Since_Last_TLog,” or “Days_Since_Last_Diff” columns. Calling with the “all_exceptions” parameter returns results sorted by recovery model, then database name.
In a follow-up post (click HERE) I will include a SQL Server Reporting Services report example which integrates nicely with this stored procedure. The “999” result is used in some of the formatting expressions in the SSRS report, so if you intend to use this report, you should not modify those parts of the procedure.
Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.
USE [master] GO CREATE PROC [dbo].[rpt_SQL_Backups_Report] @type VARCHAR(50) = 'all_exceptions' AS DECLARE @srvrName VARCHAR(500) SELECT @srvrName = @@SERVERNAME /* Created by Norm Enger 2/28/2011, based in part on a script at: http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=715 Shows status, recovery models, last full backup dates, last tlog backup dates, last diff backup dates, and days lapsed since each of those, for all dbs on a server. Works for SQL 2000 and above. The "all_alpha" sorts by db name. The "all_recovery_alpha" sorts by recovery model, then db name. The "all_exceptions" shows only ones that have TLog backups aged over 1 day, Full backups aged over 7 days, or Diff backups aged over 1 day, or any type that has never have had a backup (indicated by returning "999" in the "Days_Since_Last_Full, "Days_Since_Last_TLog", or "Days_Since_Last_Diff" results; and sorts by recovery model, then db name. Sample executions: EXEC rpt_SQL_Backups_Report 'all_exceptions' EXEC rpt_SQL_Backups_Report 'all_recovery_alpha' EXEC rpt_SQL_Backups_Report 'all_alpha' */ SET NOCOUNT ON CREATE TABLE #TEMP ( DBName VARCHAR(500), [Recovery] VARCHAR(500), [DBStatus] VARCHAR(500) ) DECLARE @dbname VARCHAR(500), @STR VARCHAR (8000) DECLARE DBRECOVERYMODELGENERATOR_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE category IN ('0', '1','16') ORDER BY name OPEN DBRECOVERYMODELGENERATOR_CURSOR FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SELECT @STR = 'SELECT ''' + @dbname + ''' AS DBName, CAST(DATABASEPROPERTYEX ('''+ @dbname + ''', ''recovery'') AS VARCHAR(500)) AS Recovery, CAST(DATABASEPROPERTYEX (''' + @dbname + ''', ''STATUS'') AS VARCHAR(500)) + CAST(CASE WHEN DATABASEPROPERTYEX(''' + @dbname + ''', ''IsInStandBy'') = 0 THEN '''' ELSE '' (Standby / Read-Only)'' END AS VARCHAR(500)) AS DBStatus' INSERT INTO #TEMP EXEC (@STR) END FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname END CLOSE DBRECOVERYMODELGENERATOR_CURSOR DEALLOCATE DBRECOVERYMODELGENERATOR_CURSOR IF @type = 'all_alpha' BEGIN SELECT a.[DBName], a.[Recovery], DBStatus = CASE WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus] ELSE '***' + a.[DBStatus] + '***' END, CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup, T.[Days_Since_Last_Full], CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup, U.[Days_Since_Last_TLog], CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup, V.[Days_Since_Last_Diff] FROM #TEMP a LEFT JOIN -- Backup of data (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'D' GROUP BY database_name --ORDER BY 3 DESC ) T ON a.[DBName] = T.[database_name] LEFT JOIN --Get the device or filename the last Full backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) T2 ON T.database_name = T2.database_name AND T.backup_finish_date = T2.backup_finish_date LEFT JOIN -- Find the backup of Transaction Log files (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'L' GROUP BY database_name ) U ON a.[DBName] = U.[database_name] LEFT JOIN --Get the device or filename the last TLog backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) U2 ON U.database_name = U2.database_name AND U.backup_finish_date = U2.backup_finish_date LEFT JOIN -- Find the differential backups (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'I' GROUP BY database_name ) V ON a.[DBName] = V.[database_name] LEFT JOIN --Get the device or filename the last diff backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) V2 ON V.database_name = V2.database_name AND V.backup_finish_date = V2.backup_finish_date ORDER BY DBName END IF @type = 'all_recovery_alpha' BEGIN SELECT ServerName = @srvrName, a.[DBName], a.[Recovery], DBStatus = CASE WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus] ELSE '***' + a.[DBStatus] + '***' END, CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup, ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full], CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup, ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog], CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup, ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff] FROM #TEMP a LEFT JOIN -- Backup of data (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'D' GROUP BY database_name --ORDER BY 3 DESC ) T ON a.[DBName] = T.[database_name] LEFT JOIN --Get the device or filename the last Full backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) T2 ON T.database_name = T2.database_name AND T.backup_finish_date = T2.backup_finish_date LEFT JOIN -- Find the backup of Transaction Log files (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'L' GROUP BY database_name ) U ON a.[DBName] = U.[database_name] LEFT JOIN --Get the device or filename the last TLog backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) U2 ON U.database_name = U2.database_name AND U.backup_finish_date = U2.backup_finish_date LEFT JOIN -- Find the differential backups (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'I' GROUP BY database_name ) V ON a.[DBName] = V.[database_name] LEFT JOIN --Get the device or filename the last diff backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) V2 ON V.database_name = V2.database_name AND V.backup_finish_date = V2.backup_finish_date ORDER BY [Recovery], DBName END IF @type = 'all_exceptions' BEGIN SELECT ServerName = @srvrName, a.[DBName], a.[Recovery], DBStatus = CASE WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus] ELSE '***' + a.[DBStatus] + '***' END, CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup, ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full], CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup, ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog], CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup, ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff] FROM #TEMP a LEFT JOIN -- Backup of data (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'D' GROUP BY database_name --ORDER BY 3 DESC ) T ON a.[DBName] = T.[database_name] LEFT JOIN --Get the device or filename the last Full backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) T2 ON T.database_name = T2.database_name AND T.backup_finish_date = T2.backup_finish_date LEFT JOIN -- Find the backup of Transaction Log files (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'L' GROUP BY database_name ) U ON a.[DBName] = U.[database_name] LEFT JOIN --Get the device or filename the last TLog backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) U2 ON U.database_name = U2.database_name AND U.backup_finish_date = U2.backup_finish_date LEFT JOIN -- Find the differential backups (SELECT database_name, MAX(backup_finish_date) AS backup_finish_date, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff FROM msdb.dbo.backupset WITH (NOLOCK) WHERE type = 'I' GROUP BY database_name ) V ON a.[DBName] = V.[database_name] LEFT JOIN --Get the device or filename the last diff backup was made to (SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name FROM msdb.dbo.backupset bs WITH (NOLOCK) INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK) ON bs.media_set_id = mf.media_set_id ) V2 ON V.database_name = V2.database_name AND V.backup_finish_date = V2.backup_finish_date WHERE ( ( T.[Days_Since_Last_Full] > 7 ) OR ( U.[Days_Since_Last_TLog] > 1 AND a.[Recovery] IN ('FULL','BULK_LOGGED') ) OR ( T.[Days_Since_Last_Full] IS NULL ) OR ( U.[Days_Since_Last_TLog] IS NULL AND a.[Recovery] IN ('FULL','BULK_LOGGED') ) OR ( a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind') AND a.[DBName] NOT LIKE 'adventurework%' AND V.[Days_Since_Last_Diff] > 1 ) OR ( a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind') AND a.[DBName] NOT LIKE 'adventurework%' AND V.[Days_Since_Last_Diff] IS NULL ) ) AND a.[DBName] NOT IN ('tempdb', 'model', 'pubs', 'northwind') AND a.[DBName] NOT LIKE 'adventurework%' ORDER BY [Recovery], DBName END DROP TABLE #TEMPPosted February 28, 2011 by Norm Enger in Microsoft SQL Server
The Tragedy in Tucson Leave a comment
I am privileged to live in the city of Tucson. I posted a blog entitled “The Tragedy in Tucson” over on my personal Web site. Click HERE to read this blog.
Detect When Your tempdb Files Have Grown Leave a comment
One goal of managing your tempdb database files should be to size them appropriately in order to avoid auto-growth. Here is a handy script that works on all versions of SQL Server (2000 and above) to show whether your tempdb data and log files have grown beyond their initial sizes (the sizes they will be upon restarting of the SQL Server services). In other words, the script will tell you if auto-growth has occurred.
In most cases, if auto-growth has occurred, you will want to increase the initial size of an individual file so that this will not occur. Changing the initial size can be done easily through the SSMS GUI by expanding Databases –> System Databases and right-clicking on tempdb –> Properties. Then in the Properties window, go to the Files tab and adjust the size.
The script displays the initial size for each tempdb file, the current file size for each file, and growth beyond the initial size that has occurred, in MB, if any. As well, it displays the logical and physical file names and paths, current used and free space within the files, and auto-growth settings (e.g., “Autogrowth: By 10 percent, unrestricted growth”).
A suggested way to use this script, which I have implemented but have not demonstrated here, is to schedule it to run against each of your SQL instances daily, and if auto-growth has occurred, notify you via email.
Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.
--tempdb - Show files info with initial tempdb size integrated USE [master] IF OBJECT_ID('tempdb..#data') IS NULL CREATE TABLE #data( dbname VARCHAR(300), dbcompat VARCHAR(10), Fileid int NOT NULL, [FileGroup] int NOT NULL, TotalExtents int NOT NULL, UsedExtents int NOT NULL, [Name] sysname NOT NULL, [FileName] varchar(300) NOT NULL, FilePath VARCHAR(500) NULL, Autogrowth VARCHAR(500) NULL ) IF OBJECT_ID('tempdb..#log') IS NULL CREATE TABLE #log( id int identity(1,1) not null, groupid INT DEFAULT 0 NULL, dbname sysname NOT NULL, dbcompat VARCHAR(10), LogSize numeric(38,7) NOT NULL, LogUsed numeric(38,6) NOT NULL, Status int NOT NULL, [Name] VARCHAR(300) NULL, FilePath VARCHAR(500) NULL, Autogrowth VARCHAR(500) NULL ) USE [tempdb] INSERT #data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName) EXEC('DBCC showfilestats with no_infomsgs') UPDATE #data SET dbname = 'tempdb' WHERE dbname IS NULL UPDATE #data SET [FilePath] = s.filename, Autogrowth = 'Autogrowth: ' + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN 'None' WHEN s.status & 0x100000 = 0 THEN 'By ' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + ' MB' ELSE 'By ' + CONVERT(VARCHAR, s.growth) + ' percent' END + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN '' WHEN CAST([maxsize]*8.0/1024 AS DEC(20,2)) <= 0.00 THEN ', unrestricted growth' ELSE ', restricted growth to ' + CAST(CAST([maxsize]*8.0/1024 AS DEC(20)) AS VARCHAR) + ' MB' END FROM #data d INNER JOIN dbo.sysfiles s ON d.FileGroup = s.groupid AND d.Fileid = s.fileid WHERE d.dbname = 'tempdb' USE [tempdb]; INSERT #log ( dbname, LogSize, LogUsed, Status ) EXEC('DBCC sqlperf(logspace) with no_infomsgs'); INSERT #log ( dbname, [groupid], LogSize, LogUsed, Status, [Name] ) SELECT 'tempdb', [s].[groupid], s.[size] * 0.0078125 AS [size], 0, 0, [name] FROM dbo.sysfiles s WHERE [groupid] = 0; USE [tempdb]; UPDATE #log SET [#log].[LogUsed] = b.[LogUsed] FROM [#log] t INNER JOIN ( SELECT [l].[dbname], [LogUsed] FROM #log l LEFT JOIN ( SELECT dbname, Name, MIN(id) AS id FROM #log GROUP BY dbname, Name ) k ON l.dbname = k.dbname AND l.Name = k.Name AND l.id = k.id WHERE k.id IS NULL ) AS b ON [t].[dbname] = [b].[dbname]; DELETE #log WHERE Name IS NULL; USE [master]; UPDATE #log SET [Name] = s.name, [FilePath] = s.filename, Autogrowth = 'Autogrowth: ' + CASE WHEN ( s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00 ) OR s.growth = 0 THEN 'None' WHEN s.status & 0x100000 = 0 THEN 'By ' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + ' MB' ELSE 'By ' + CONVERT(VARCHAR, s.growth) + ' percent' END + CASE WHEN ( s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00 ) OR s.growth = 0 THEN '' WHEN CAST([maxsize]*8.0/1024 AS DEC(38,7)) <= 0.00 THEN ', unrestricted growth' ELSE ', restricted growth to ' + CAST(CAST([maxsize]*8.0/1024 AS DEC(38,7)) AS VARCHAR) + ' MB' END FROM #log l INNER JOIN [tempdb].dbo.sysfiles s ON l.groupid = s.groupid AND l.[Name] COLLATE DATABASE_DEFAULT = s.[name] COLLATE DATABASE_DEFAULT WHERE l.dbname = 'tempdb'; --SELECT * FROM [#data] --SELECT * FROM [#log] WHERE dbname = 'tempdb' SELECT DB_Name, [Type], [Name], [FilePath], init_size_MB, [CurrTotalMB], tempdb_growth_MB = CASE WHEN [CurrTotalMB] - init_size_MB < 0 THEN 0 ELSE [CurrTotalMB] - init_size_MB END, UsedMB, FreeMB, Autogrowth FROM ( SELECT dbname AS [DB_Name], 'Data File' AS [Type], [Name] AS [Name], [FilePath], init_size_MB = a.Initial_Size_MB, (TotalExtents*64)/1024.0 AS [CurrTotalMB], (UsedExtents*64)/1024.0 AS [UsedMB], (TotalExtents*64)/1024.0 - (UsedExtents*64)/1024.0 AS [FreeMB], [Autogrowth] FROM #data d INNER JOIN ( SELECT [DB_Name] = 'tempdb', f.[name] AS logical_file_Name, (CAST(f.[size] AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB FROM master..sysaltfiles f INNER JOIN master..sysdatabases d ON f.dbid = d.dbid WHERE d.name = 'tempdb') AS a ON d.dbname = a.[DB_Name] AND d.[Name] = a.logical_file_Name WHERE [dbname] = 'tempdb' UNION SELECT dbname AS [DB_Name], 'TLog File', [Name] AS [NAME], [FilePath], init_size_MB = a.Initial_Size_MB, LogSize, ((LogUsed/100)*LogSize), LogSize - ((LogUsed/100)*LogSize), [Autogrowth] FROM #log l INNER JOIN ( SELECT [DB_Name] = 'tempdb', f.[name] AS logical_file_Name, (CAST(f.[size] AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB FROM master..sysaltfiles f INNER JOIN master..sysdatabases d ON f.dbid = d.dbid WHERE d.name = 'tempdb') AS a ON l.dbname = a.[DB_Name] AND l.[Name] = a.logical_file_Name WHERE [dbname] = 'tempdb' ) AS r ORDER BY 1, [Type], [Name] IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE [#data] IF OBJECT_ID('tempdb..#log') IS NOT NULL DROP TABLE [#log]Posted January 1, 2011 by Norm Enger in Microsoft SQL Server
Applying SP2 or SP3 to a Rebuilt SQL 2005 Cluster Node Leave a comment
Recently I ran into an issue with rebuilding the nodes of a two-node MSCS SQL 2005 64 bit cluster. We were rebuilding one node at a time in order to convert the machines to be boot-from-SAN. This is so that the system drives could participate in SAN-level replication taking place to an off-site DR location. The SQL instances were patched to SP3 plus a post-SP3 patch level.
These are running on Windows 2003 Ent. Ed. SP2. As an interesting side note, to enable Windows 2003 clustering to be able to manage the SAN disks as clustered or shared disks, you will need to add a registry entry, per the article, “How to add a registry value to a Windows Server 2003-based computer that you start from a SAN so that the startup disk, the pagefile disks, and the cluster disks are all on the same SAN fabric.”
Back to the topic at hand. The issue we ran into was that after rebuilding a node, and after installing the RTM version of SQL 2005 to the rebuilt node (let’s say Node “A” in this case), we could not get the SP3 installer to stop trying to apply the patching to the existing (already-patched) node (let’s say Node “B”). What would happen is that the installer would try to update the passive node (“B”) first, and would fail and quit the process every time, before updating the rebuilt node.
It turns out the best solution for this is to temporarily set the “Possible Owners” settings for each individual resource in your SQL cluster groups, using Cluster Administrator, to be, in our example, the “A” node, i.e., temporarily remove the other nodes (“B” in our example) as possible owners. Once these were set, we were able to successfully apply the SP3 update and post-SP3 updates needed, in order to bring the rebuilt node (“A”) to the same build level as the existing node.
I am not entirely sure why this was necessary, but it seems the installer program decides which nodes to run the patching against based on the “Possible Owners” settings for the SQL groups’ resources.
Afterwards when patching is complete, be sure to set the “Possible Owners” settings back to the way they were. After this you will have both machines properly patched to the same build level, as they should be. You would apply this same technique later when rebuilding the “B” Node.
SQL Server 2000/2005 Security Audit Scripts 1 comment
Originally published Saturday, June 30, 2007 at http://www.biblereadingschedule.com/nucleus?itemid=62&catid=6
Here is a series of discreet scripts designed to assist in the security audit of a SQL 2000 or 2005 instance. I have never seen scripts of this type gathered together in one place. I have made modifications where applicable to allow these scripts to audit all of the databases in a SQL instance in one run, making all of these suitable for a SQL instance security report, using tools such as Reporting Services.
Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.
/*
Norm Enger, 6/2007. The following is a series of discreet scripts designed
to assist in the security audit of a SQL 2000 or 2005 instance. Many of the ideas
for the scripts and some of the code came from FullSun SQLServerToolKit
(http://www2.fullsun.com:8000/Software/SQLServerToolKit/SQLServerToolKit.htm) or
other sources, and I have given credit where possible.
I have never seen scripts of this type gathered together in one place, which
seems odd since security is such an important aspect of SQL Server. I have made
modifications where applicable to allow these scripts to audit all of the
databases in a SQL instance in one run, making all of them suitable for a
SQL instance security report, using tools such as Reporting Services.
Happy auditing!
*/
--Detect server wide roles assigned to users - sysadmins and other roles - works with both SQL 2000 and 2005
USE master
--Sysadmins
SELECT
name AS Login,
sysadmin =
CASE
WHEN sysadmin = 1 THEN 'X'
ELSE ''
END,
securityadmin =
CASE
WHEN securityadmin = 1 THEN 'X'
ELSE ''
END,
serveradmin =
CASE
WHEN serveradmin = 1 THEN 'X'
ELSE ''
END,
setupadmin =
CASE
WHEN setupadmin = 1 THEN 'X'
ELSE ''
END,
processadmin =
CASE
WHEN processadmin = 1 THEN 'X'
ELSE ''
END,
diskadmin =
CASE
WHEN diskadmin = 1 THEN 'X'
ELSE ''
END,
dbcreator =
CASE
WHEN dbcreator = 1 THEN 'X'
ELSE ''
END,
bulkadmin =
CASE
WHEN bulkadmin = 1 THEN 'X'
ELSE ''
END,
CONVERT(CHAR(16),createdate,120) AS 'DateCreated'
FROM master.dbo.syslogins
WHERE
sysadmin = 1
ORDER BY NAME
GO
--Any other server-wide role, not including ones in the first list above. Works with both SQL 2000 and 2005
SELECT
name AS Login,
securityadmin =
CASE
WHEN securityadmin = 1 THEN 'X'
ELSE ''
END,
serveradmin =
CASE
WHEN serveradmin = 1 THEN 'X'
ELSE ''
END,
setupadmin =
CASE
WHEN setupadmin = 1 THEN 'X'
ELSE ''
END,
processadmin =
CASE
WHEN processadmin = 1 THEN 'X'
ELSE ''
END,
diskadmin =
CASE
WHEN diskadmin = 1 THEN 'X'
ELSE ''
END,
dbcreator =
CASE
WHEN dbcreator = 1 THEN 'X'
ELSE ''
END,
bulkadmin =
CASE
WHEN bulkadmin = 1 THEN 'X'
ELSE ''
END,
CONVERT(CHAR(16),createdate,120) AS 'DateCreated'
FROM master.dbo.syslogins
WHERE
(securityadmin = 1
OR serveradmin = 1
OR setupadmin = 1
OR processadmin = 1
OR diskadmin = 1
OR dbcreator = 1
OR bulkadmin = 1)
AND sysadmin <> 1
ORDER BY NAME
USE master
GO
--Blank or easily guessed passwords. Works with both SQL 2000 and 2005.
IF OBJECT_ID('dbo.spAuditPasswords') IS NOT NULL
DROP PROCEDURE dbo.spAuditPasswords
GO
CREATE PROCEDURE dbo.spAuditPasswords
AS
/*
Modified by Norm Enger 6/14/2007. Works with both SQL 2000 and 2005.
Original script obtained courtesy of Randy Dyess, from
http://www.fits-consulting.de/blog/PermaLink,guid,4f42c907-3e39-4283-a4fb-c3e536ce2ceb.aspx
Creation Date: 03/22/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Perform a simple audit of user's passwords
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates: None
Date Author Purpose
*/
SET NOCOUNT ON
--Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)
--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin sysname NULL
,lngPass integer NULL
,Password varchar(500) NULL
,Type int NULL
)
--Insert non ntuser into temp table
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins WHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT
--Determine if password and name are the ssame
SET @lngCounter = @lngLogCount
WHILE @lngCounter <> 0
BEGIN
SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
UPDATE #tLogins
SET
lngPass = (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.syslogins WHERE name = @strName))),
Type =
CASE
WHEN (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.syslogins WHERE name = @strName))) = 1 THEN 2 -- Password same as login
ELSE NULL
END
WHERE numID = @lngCounter
AND Type IS NULL
SET @lngCounter = @lngCounter - 1
END
--Reset column for next password test
UPDATE #tLogins
SET lngPass = 0
--Determine if password is only one character long
SET @lngCounter = @lngLogCount
WHILE @lngCounter <> 0
BEGIN
SET @lngCounter1 = 1
SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
WHILE @lngCounter1 < 256
BEGIN
UPDATE #tLogins
SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.syslogins WHERE name = @strName))), Password = UPPER(CHAR(@lngCounter1)) + ' or ' + LOWER(CHAR(@lngCounter1)),
Type =
CASE
WHEN (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.syslogins WHERE name = @strName))) = 1 THEN 3 --password is only one character long
ELSE NULL
END
WHERE numID = @lngCounter
AND lngPass <> 1
AND Type IS NULL
SET @lngCounter1 = @lngCounter1 + 1
END
SET @lngCounter = @lngCounter - 1
END
--Return combined results
SELECT name AS 'Login Name', Passsword = '(BLANK)' FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0
UNION ALL
SELECT strLogin AS 'Login Name', Password = strLogin FROM #tLogins WHERE Type = 2
UNION ALL
SELECT 'Login Name' = strLogin, Password FROM #tLogins WHERE Type = 3
ORDER BY name
GO
--Detect disabled or deleted Windows logins; works with both SQL 2000 and 2005
USE master
EXEC sp_validatelogins
GO
--Detect logins created in last 30 days
SELECT
name AS 'Login Name',
CONVERT(CHAR(16),createdate,120) AS 'Date Created'
FROM master.dbo.syslogins
WHERE createdate >= DATEADD(dd,-30,GETDATE())
ORDER BY createdate DESC
GO
--Detect orphan users in all dbs on a SQL instance; works in both SQL 2000 and 2005.
USE master
GO
IF OBJECT_ID('rpt_security_detect_db_orphans') IS NOT NULL
DROP PROCEDURE [dbo].[rpt_security_detect_db_orphans]
GO
CREATE PROCEDURE dbo.rpt_security_detect_db_orphans
AS
/*
NE 6/11/2007 - Detect orphan users in all dbs on a SQL instance; works in both SQL 2000 and 2005.
EXEC rpt_security_detect_db_orphans
*/
DECLARE
@dbname varchar(200),
@sql varchar(8000)
DECLARE @temp table
(
dbname VARCHAR(500)
)
CREATE TABLE #dbOrphanUsers
(
DbName varchar(500),
[User] varchar(500)
)
INSERT INTO @temp
SELECT name
FROM sysdatabases
WHERE
category IN ('0', '1','16')
AND
name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
AND
name NOT LIKE 'adventurework%'
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
ORDER BY name
SELECT @dbname = MIN(dbname) FROM @temp
WHILE @dbname IS NOT NULL
BEGIN
SET @sql =
'INSERT INTO #dbOrphanUsers
(DbName, [User])
SELECT DbName = ''' + @dbname + ''', name AS [User]
FROM [' + @dbname + '].dbo.sysusers
WHERE
issqluser = 1
and (sid is not null
and sid <> 0x0)
and suser_sname(sid) is null'
EXEC(@sql)
SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname
END
SELECT * FROM #dbOrphanUsers ORDER BY DbName, [User]
DROP TABLE #dbOrphanUsers
GO
-- Detect User permissions for all databases and users - SQL 2000 version only
-- Also incuded below is a modified version of sp_helprotect (named as sp_helprotect2) to be called by this.
USE master
GO
IF OBJECT_ID('rpt_security_detect_user_permissions_2000') IS NOT NULL
DROP PROCEDURE [dbo].[rpt_security_detect_user_permissions_2000]
GO
CREATE PROCEDURE dbo.rpt_security_detect_user_permissions_2000
AS
/*
NE 6/11/2007 - Detect User permissions for all databases and users - SQL 2000 version only.
EXEC rpt_security_detect_user_permissions_2000
*/
SET NOCOUNT ON
CREATE TABLE #Permissions (dbname varchar(500), UserName varchar(500), owner varchar(500),object varchar(500),Grantee varchar(500),Grantor varchar(500),ProtectType varchar(500),Act varchar(500),Col varchar(50))
CREATE TABLE #Roles (dbname varchar(500), UserName varchar(500),GroupName varchar(500),LoginName varchar(500),DefDBName varchar(500),UserID varchar(500),SID varchar(50))
DECLARE
@dbname varchar(200),
@sql varchar(8000)
DECLARE @temp table
(
dbname VARCHAR(500)
)
INSERT INTO @temp
SELECT name
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE
category IN ('0', '1','16')
AND
name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
AND
name NOT LIKE 'adventurework%'
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
ORDER BY name
SELECT @dbname = MIN(dbname) FROM @temp
WHILE @dbname IS NOT NULL
BEGIN
SET @sql =
'use [' + @dbname + '];
declare @users table([User] varchar(500));
declare @currUser varchar(500);
insert into @users
SELECT [name]
FROM [' + @dbname + '].dbo.sysusers WITH (NOLOCK)
WHERE hasdbaccess = 1 AND [name] <> ''dbo''
SELECT @currUser = min([User]) from @users;
WHILE @currUser IS NOT NULL
BEGIN
insert into #Permissions
(owner, object, Grantee, Grantor, ProtectType, Act ,Col)
exec sp_helprotect2 @username = @currUser
insert into #Roles
(UserName, GroupName, LoginName, DefDBName, UserID, SID)
exec sp_helpuser @name_in_db = @currUser
update #Permissions set dbname = ''' + @dbname + ''', UserName = @currUser where dbname is null
update #Roles set dbname = ''' + @dbname + ''' where dbname is null
select @currUser = min([User]) from @users where [User] > @currUser
END'
EXEC(@sql)
SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname
END
SELECT
dbname AS DB_Name,
UserName,
'Permissions' = 'SP_AddRoleMember ''' + RTRIM(GroupName)+''', '''+ RTRIM(UserName)+''''
FROM #Roles
UNION
SELECT
dbname AS DB_Name,
UserName,
UPPER(RTRIM(ProtectType))+' '+UPPER(RTRIM(Act))+' ON ['+owner+'].['+object+']'+
CASE
WHEN (PATINDEX('%All%', Col)=0) AND (Col <> '.') THEN ' ('+Col+')'
ELSE ''
END
+ ' TO ['+Grantee+']'
FROM #Permissions
ORDER BY dbname, UserName, Permissions DESC
drop table #Permissions
drop table #Roles
GO
-------------------
--Modified version of sp_helprotect (named as sp_helprotect2) - must be used with the SQL 2000 only version above...
USE master
GO
IF OBJECT_ID('sp_helprotect2') IS NOT NULL
DROP PROCEDURE dbo.sp_helprotect2
GO
CREATE PROCEDURE dbo.sp_helprotect2
@name ncharacter varying(776) = NULL
,@username sysname = NULL
,@grantorname sysname = NULL
,@permissionarea character varying(10) = 'o s'
as
/********
Explanation of the parms...
---------------------------
@name: Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
Examples- 'user2.tb' , 'CREATE TABLE', null
@username: Name of the grantee (for sysprotects.uid).
Examples- 'user2', null
@grantorname: Name of the grantor (for sysprotects.grantor).
Examples- 'user2' --Would prevent report rows which would
-- have 'dbo' as grantor.
@permissionarea: O=Object, S=Statement; include all which apply.
Examples- 'o' , ',s' , 'os' , 'so' , 's o' , 's,o'
GeneMi
********/
Set nocount on
Declare
@vc1 sysname
,@Int1 integer
Declare
@charMaxLenOwner character varying(11)
,@charMaxLenObject character varying(11)
,@charMaxLenGrantee character varying(11)
,@charMaxLenGrantor character varying(11)
,@charMaxLenAction character varying(11)
,@charMaxLenColumnName character varying(11)
Declare
@OwnerName sysname
,@ObjectStatementName sysname
/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
( Id int Null
,Type1Code char(6) collate database_default NOT Null
,ObjType char(2) collate database_default Null
,ActionName varchar(20) collate database_default Null
,ActionCategory char(2) collate database_default Null
,ProtectTypeName char(10) collate database_default Null
,Columns_Orig varbinary(32) Null
,OwnerName sysname collate database_default NOT Null
,ObjectName sysname collate database_default NOT Null
,GranteeName sysname collate database_default NOT Null
,GrantorName sysname collate database_default NOT Null
,ColumnName sysname collate database_default Null
,ColId smallint Null
,Max_ColId smallint Null
,All_Col_Bits_On tinyint Null
,new_Bit_On tinyint Null ) -- 1=yes on
/* Check for valid @permissionarea */
Select @permissionarea = upper( isnull(@permissionarea,'?') )
IF ( charindex('O',@permissionarea) <= 0
AND charindex('S',@permissionarea) <= 0)
begin
raiserror(15300,-1,-1 ,@permissionarea,'o,s')
return (1)
end
select @vc1 = parsename(@name,3)
/* Verified db qualifier is current db*/
IF (@vc1 is not null and @vc1 <> db_name())
begin
raiserror(15302,-1,-1) --Do not qualify with DB name.
return (1)
end
/* Derive OwnerName and @ObjectStatementName*/
select @OwnerName = parsename(@name, 2)
,@ObjectStatementName = parsename(@name, 1)
IF (@ObjectStatementName is NULL and @name is not null)
begin
raiserror(15253,-1,-1,@name)
return (1)
end
/* Copy info from sysprotects for processing */
IF charindex('O',@permissionarea) > 0
begin
/* Copy info for objects */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On )
/* 1Regul indicates action can be at column level,
2Simpl indicates action is at the object level */
SELECT id
,case
when columns is null then '2Simpl'
else '1Regul'
end
,Null
,val1.name
,'Ob'
,val2.name
,columns
,user_name(objectproperty( id, 'ownerid' ))
,object_name(id)
,user_name(uid)
,user_name(grantor)
,case
when columns is null then '.'
else Null
end
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@OwnerName is null or user_name(objectproperty( id, 'ownerid' )) = @OwnerName)
and (@ObjectStatementName is null or object_name(id) = @ObjectStatementName)
and (@username is null or user_name(uid) = @username)
and (@grantorname is null or user_name(grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and val2.type = 'T' --T is overloaded.
and val2.number = sysp.protecttype
and sysp.id != 0
IF EXISTS (SELECT * From #t1_Prots)
begin
UPDATE #t1_Prots set ObjType = ob.xtype
FROM sysobjects ob
WHERE ob.id = #t1_Prots.Id
UPDATE #t1_Prots
set Max_ColId = (select max(colid) from syscolumns sysc
where #t1_Prots.Id = sysc.id) -- colid may not consecutive if column dropped
where Type1Code = '1Regul'
/* First bit set indicates actions pretains to new columns. (i.e. table-level permission)
Set new_Bit_On accordinglly */
UPDATE #t1_Prots SET new_Bit_On =
CASE convert(int,substring(Columns_Orig,1,1)) & 1
WHEN 1 then 1
ELSE 0
END
WHERE ObjType <> 'V' and Type1Code = '1Regul'
/* Views don't get new columns */
UPDATE #t1_Prots set new_Bit_On = 0
WHERE ObjType = 'V'
/* Indicate enties where column level action pretains to all
columns in table All_Col_Bits_On = 1 */
UPDATE #t1_Prots set All_Col_Bits_On = 1
where #t1_Prots.Type1Code = '1Regul'
and not exists
(select *
from syscolumns sysc, master..spt_values v
where #t1_Prots.Id = sysc.id and sysc.colid = v.number
and v.number <= Max_ColId -- column may be dropped/added after Max_ColId snap-shot
and v.type = 'P' and
/* Columns_Orig where first byte is 1 means off means on and on mean off
where first byte is 0 means off means off and on mean on */
case convert(int,substring(#t1_Prots.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
else (~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
end & v.high = 0)
/* Indicate entries where column level action pretains to
only some of columns in table All_Col_Bits_On = 0*/
UPDATE #t1_Prots set All_Col_Bits_On = 0
WHERE #t1_Prots.Type1Code = '1Regul'
and All_Col_Bits_On is null
Update #t1_Prots
set ColumnName =
case
when All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
when All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
when All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
end
from #t1_Prots
where ObjType IN ('S ' ,'U ', 'V ')
and Type1Code = '1Regul'
and NOT (All_Col_Bits_On = 0 and new_Bit_On = 0)
/* Expand and Insert individual column permission rows */
INSERT into #t1_Prots
(Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId )
SELECT prot1.Id
,'1Regul'
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,col_name ( prot1.Id ,val1.number )
,val1.number
from #t1_Prots prot1
,master.dbo.spt_values val1
,syscolumns sysc
where prot1.ObjType IN ('S ' ,'U ' ,'V ')
and prot1.All_Col_Bits_On = 0
and prot1.Id = sysc.id
and val1.type = 'P'
and val1.number = sysc.colid
and
case convert(int,substring(prot1.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
else (~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
end & val1.high <> 0
delete from #t1_Prots
where ObjType IN ('S ' ,'U ' ,'V ')
and All_Col_Bits_On = 0
and new_Bit_On = 0
end
end
/* Handle statement permissions here*/
IF (charindex('S',@permissionarea) > 0)
begin
/* All statement permissions are 2Simpl */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On )
SELECT id
,'2Simpl'
,Null
,val1.name
,'St'
,val2.name
,columns
,'.'
,'.'
,user_name(sysp.uid)
,user_name(sysp.grantor)
,'.'
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@username is null or user_name(sysp.uid) = @username)
and (@grantorname is null or user_name(sysp.grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and (@ObjectStatementName is null or val1.name = @ObjectStatementName)
and val2.number = sysp.protecttype
and val2.type = 'T'
and sysp.id = 0
end
IF NOT EXISTS (SELECT * From #t1_Prots)
begin
-- Commented out by NE - 6/12/2007
-- raiserror(15330,-1,-1)
return (1)
end
/* Calculate dynamic display col widths */
SELECT
@charMaxLenOwner =
convert ( varchar, max(datalength(OwnerName)))
,@charMaxLenObject =
convert ( varchar, max(datalength(ObjectName)))
,@charMaxLenGrantee =
convert ( varchar, max(datalength(GranteeName)))
,@charMaxLenGrantor =
convert ( varchar, max(datalength(GrantorName)))
,@charMaxLenAction =
convert ( varchar, max(datalength(ActionName)))
,@charMaxLenColumnName =
convert ( varchar, max(datalength(ColumnName)))
from #t1_Prots
/* Output the report */
EXECUTE(
'Set nocount off
SELECT ''Owner'' = substring (OwnerName ,1 ,' + @charMaxLenOwner + ')
,''Object'' = substring (ObjectName ,1 ,' + @charMaxLenObject + ')
,''Grantee'' = substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')
,''Grantor'' = substring (GrantorName ,1 ,' + @charMaxLenGrantor + ')
,''ProtectType''= ProtectTypeName
,''Action'' = substring (ActionName ,1 ,' + @charMaxLenAction + ')
,''Column'' = substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
from #t1_Prots
order by
ActionCategory
,Owner ,Object
,Grantee ,Grantor
,ProtectType ,Action
,ColId --Multiple -123s ( <0 ) possible
Set nocount on'
)
Return (0) -- sp_helprotect2
GO
-- Detect User permissions for all databases and users - SQL 2005 version only
-- Don't need modified version of sp_helprotect (named as sp_helprotect2) in SQL 2005.
USE master
GO
IF OBJECT_ID('rpt_security_detect_user_permissions_2005') IS NOT NULL
DROP PROCEDURE [dbo].[rpt_security_detect_user_permissions_2005]
GO
CREATE PROCEDURE dbo.rpt_security_detect_user_permissions_2005
AS
/*
NE 6/11/2007 - Detect User permissions for all databases and users - SQL 2005 version only.
EXEC rpt_security_detect_user_permissions_2005
*/
SET NOCOUNT ON
CREATE TABLE #Permissions (dbName varchar(500), UserName varchar(500), owner varchar(500),object varchar(500),Grantee varchar(500),Grantor varchar(500),ProtectType varchar(500),Act varchar(500),Col varchar(500))
CREATE TABLE #Roles (dbName varchar(500), UserName varchar(500),GroupName varchar(500),LoginName varchar(500),DefDBName varchar(500), DefSchemaName varchar(500), UserID varchar(500),SID varchar(50))
DECLARE
@dbname varchar(500),
@sql varchar(8000)
DECLARE @temp table
(
dbname VARCHAR(500)
)
INSERT INTO @temp
SELECT name
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE
category IN ('0', '1','16')
AND
name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
AND
name NOT LIKE 'adventurework%'
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
ORDER BY name
SELECT @dbname = MIN(dbname) FROM @temp
WHILE @dbname IS NOT NULL
BEGIN
SET @sql =
'use [' + @dbname + '];
declare @users table([User] varchar(500));
declare @currUser varchar(500);
insert into @users
SELECT [name]
FROM [' + @dbname + '].dbo.sysusers WITH (NOLOCK)
WHERE hasdbaccess = 1 AND [name] <> ''dbo''
SELECT @currUser = min([User]) from @users;
WHILE @currUser IS NOT NULL
BEGIN
insert into #Permissions
(owner, object, Grantee, Grantor, ProtectType, Act ,Col)
exec sp_helprotect @username = @currUser
insert into #Roles
(UserName, GroupName, LoginName, DefDBName, DefSchemaName, UserID, SID)
exec sp_helpuser @name_in_db = @currUser
update #Permissions set dbname = ''' + @dbname + ''', UserName = @currUser where dbname is null
update #Roles set dbname = ''' + @dbname + ''' where dbname is null
select @currUser = min([User]) from @users where [User] > @currUser
END
'
EXEC(@sql)
SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname
END
SELECT
dbname AS DB_Name,
UserName,
'Permissions' = 'SP_AddRoleMember ''' + RTRIM(GroupName)+''', '''+ RTRIM(username)+''''
FROM #Roles
UNION
SELECT
dbname AS DB_Name,
UserName,
UPPER(RTRIM(ProtectType))+' '+UPPER(RTRIM(Act))+' ON ['+owner+'].['+object+']'+
CASE
WHEN (PATINDEX('%All%', Col)=0) AND (Col <> '.') THEN ' ('+Col+')'
ELSE ''
END
+ ' TO ['+Grantee+']'
FROM #Permissions
ORDER BY dbname, UserName, Permissions DESC
drop table #Permissions
drop table #Roles
GO
--NE - 6/11/2007 - Detect Schemas to which a user belongs within a database; SQL 2005 only.
USE master
GO
IF OBJECT_ID('rpt_security_detect_user_schema_permissions_2005') IS NOT NULL
DROP PROCEDURE [dbo].[rpt_security_detect_user_schema_permissions_2005]
GO
CREATE PROCEDURE dbo.rpt_security_detect_user_schema_permissions_2005
AS
/*
NE 6/11/2007 - Detect User schema permissions for all databases and users - SQL 2005 only.
EXEC rpt_security_detect_user_schema_permissions_2005
*/
DECLARE
@dbname varchar(200),
@sql varchar(8000)
DECLARE @temp table
(
dbname VARCHAR(500)
)
--The following temporary table is derived from BOL...
CREATE TABLE #prmssnTypes
(Code varchar(200), PermissionName varchar(500))
CREATE TABLE #schemaPermissions
(
DB_Name varchar(500),
[User] varchar(500),
SchemaName varchar(200),
[State] varchar(200),
PermissionName varchar(500),
[Grantor] varchar(200)
)
INSERT INTO #prmssnTypes
SELECT 'AL', 'ALTER'
UNION ALL
SELECT 'ALAK', 'ALTER ANY ASYMMETRIC KEY'
UNION ALL
SELECT 'ALAR', 'ALTER ANY APPLICATION ROLE'
UNION ALL
SELECT 'ALAS', 'ALTER ANY ASSEMBLY'
UNION ALL
SELECT 'ALCF', 'ALTER ANY CERTIFICATE'
UNION ALL
SELECT 'ALDS', 'ALTER ANY DATASPACE'
UNION ALL
SELECT 'ALED', 'ALTER ANY DATABASE EVENT NOTIFICATION'
UNION ALL
SELECT 'ALFT', 'ALTER ANY FULLTEXT CATALOG'
UNION ALL
SELECT 'ALMT', 'ALTER ANY MESSAGE TYPE'
UNION ALL
SELECT 'ALRL', 'ALTER ANY ROLE'
UNION ALL
SELECT 'ALRT', 'ALTER ANY ROUTE'
UNION ALL
SELECT 'ALSB', 'ALTER ANY REMOTE SERVICE BINDING'
UNION ALL
SELECT 'ALSC', 'ALTER ANY CONTRACT'
UNION ALL
SELECT 'ALSK', 'ALTER ANY SYMMETRIC KEY'
UNION ALL
SELECT 'ALSM', 'ALTER ANY SCHEMA'
UNION ALL
SELECT 'ALSV', 'ALTER ANY SERVICE'
UNION ALL
SELECT 'ALTG', 'ALTER ANY DATABASE DDL TRIGGER'
UNION ALL
SELECT 'ALUS', 'ALTER ANY USER'
UNION ALL
SELECT 'AUTH', 'AUTHENTICATE'
UNION ALL
SELECT 'BADB', 'BACKUP DATABASE'
UNION ALL
SELECT 'BALO', 'BACKUP LOG'
UNION ALL
SELECT 'CL', 'CONTROL'
UNION ALL
SELECT 'CO', 'CONNECT'
UNION ALL
SELECT 'CORP', 'CONNECT REPLICATION'
UNION ALL
SELECT 'CP', 'CHECKPOINT'
UNION ALL
SELECT 'CRAG', 'CREATE AGGREGATE'
UNION ALL
SELECT 'CRAK', 'CREATE ASYMMETRIC KEY'
UNION ALL
SELECT 'CRAS', 'CREATE ASSEMBLY'
UNION ALL
SELECT 'CRCF', 'CREATE CERTIFICATE'
UNION ALL
SELECT 'CRDB', 'CREATE DATABASE'
UNION ALL
SELECT 'CRDF', 'CREATE DEFAULT'
UNION ALL
SELECT 'CRED', 'CREATE DATABASE DDL EVENT NOTIFICATION'
UNION ALL
SELECT 'CRFN', 'CREATE FUNCTION'
UNION ALL
SELECT 'CRFT', 'CREATE FULLTEXT CATALOG'
UNION ALL
SELECT 'CRMT', 'CREATE MESSAGE TYPE'
UNION ALL
SELECT 'CRPR', 'CREATE PROCEDURE'
UNION ALL
SELECT 'CRQU', 'CREATE QUEUE'
UNION ALL
SELECT 'CRRL', 'CREATE ROLE'
UNION ALL
SELECT 'CRRT', 'CREATE ROUTE'
UNION ALL
SELECT 'CRRU', 'CREATE RULE'
UNION ALL
SELECT 'CRSB', 'CREATE REMOTE SERVICE BINDING'
UNION ALL
SELECT 'CRSC', 'CREATE CONTRACT'
UNION ALL
SELECT 'CRSK', 'CREATE SYMMETRIC KEY'
UNION ALL
SELECT 'CRSM', 'CREATE SCHEMA'
UNION ALL
SELECT 'CRSN', 'CREATE SYNONYM'
UNION ALL
SELECT 'CRSV', 'CREATE SERVICE'
UNION ALL
SELECT 'CRTB', 'CREATE TABLE'
UNION ALL
SELECT 'CRTY', 'CREATE TYPE'
UNION ALL
SELECT 'CRVW', 'CREATE VIEW'
UNION ALL
SELECT 'CRXS', 'CREATE XML SCHEMA COLLECTION'
UNION ALL
SELECT 'DL', 'DELETE'
UNION ALL
SELECT 'EX', 'EXECUTE'
UNION ALL
SELECT 'IM', 'IMPERSONATE'
UNION ALL
SELECT 'IN', 'INSERT'
UNION ALL
SELECT 'RC', 'RECEIVE'
UNION ALL
SELECT 'RF', 'REFERENCES'
UNION ALL
SELECT 'SL', 'SELECT'
UNION ALL
SELECT 'SN', 'SEND'
UNION ALL
SELECT 'SPLN', 'SHOWPLAN'
UNION ALL
SELECT 'SUQN', 'SUBSCRIBE QUERY NOTIFICATIONS'
UNION ALL
SELECT 'TO', 'TAKE OWNERSHIP'
UNION ALL
SELECT 'UP', 'UPDATE'
UNION ALL
SELECT 'VW', 'VIEW DEFINITION'
UNION ALL
SELECT 'VWDS', 'VIEW DATABASE STATE'
INSERT INTO @temp
SELECT name
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE
category IN ('0', '1','16')
AND
name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
AND
name NOT LIKE 'adventurework%'
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
ORDER BY name
SELECT @dbname = MIN(dbname) FROM @temp
WHILE @dbname IS NOT NULL
BEGIN
SET @sql =
'use [' + @dbname + ']
declare @users table([User] varchar(500));
declare @currUser varchar(500);
insert into @users
SELECT [name]
FROM [' + @dbname + '].dbo.sysusers WITH (NOLOCK)
WHERE hasdbaccess = 1 AND [name] <> ''dbo''
SELECT @currUser = min([User]) from @users;
WHILE @currUser IS NOT NULL
BEGIN
INSERT INTO #schemaPermissions
(SchemaName, [State], PermissionName, [Grantor])
SELECT
s.name AS SchemaName,
prmssn.state_desc as [State],
t.PermissionName,
ISNULL(grantor_principal.name, '''') AS [Grantor]
FROM
[' + @dbname + '].sys.schemas s
INNER JOIN [' + @dbname + '].sys.database_permissions prmssn
ON prmssn.major_id = s.schema_id
AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN [' + @dbname + '].sys.database_principals grantor_principal
ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN [' + @dbname + '].sys.database_principals grantee_principal
ON grantee_principal.principal_id = prmssn.grantee_principal_id
LEFT JOIN #prmssnTypes t
ON prmssn.type = t.Code COLLATE Latin1_General_CI_AS_KS_WS
WHERE grantee_principal.name = @currUser
UPDATE #schemaPermissions SET DB_Name = ''' + @dbname + ''', [User] = @currUser WHERE DB_Name IS NULL
select @currUser = min([User]) from @users where [User] > @currUser
END'
EXEC(@sql)
SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname
END
SELECT
DB_Name,
[User],
SchemaName,
[State],
PermissionName,
[Grantor],
Statement = 'USE ' + DB_Name + '; ' + [STATE] + ' ' + PermissionName + ' ON SCHEMA::[' + SchemaName + '] TO [' + [User] + ']'
FROM #schemaPermissions
ORDER BY DB_Name, [User], SchemaName, [State], PermissionName, [Grantor]
DROP TABLE #schemaPermissions
DROP TABLE #prmssnTypes
GO
SQL Server Security Audit Script – Detect Logins With No Permissions – SQL 2000 Version 1 comment
Originally published Thursday, July 12, 2007 at http://www.biblereadingschedule.com/nucleus?itemid=65&catid=6
This procedure displays any logins (NT or SQL logins) which have no SQL level or database permissions. This version is for SQL 2000 only. You will also find the SQL 2005 version of this in a subsequent post. These logins should be able to be dropped from SQL.
Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.
USE [master]
GO
IF OBJECT_ID(N'[dbo].[rpt_security_Logins_with_no_permissions_2000]') IS NOT NULL
DROP PROCEDURE [dbo].[rpt_security_Logins_with_no_permissions_2000]
GO
CREATE PROC [dbo].[rpt_security_Logins_with_no_permissions_2000]
AS
/*
NE - 7/11/2007 - SQL 2000 only
Displays any SQL logins which have no server-wide or database permissions.
These logins should be able to be dropped from the SQL server
Original script courtesy of SQLServerCentral.com,
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1084
EXEC rpt_security_Logins_with_no_permissions_2000
*/
SET NOCOUNT ON
DECLARE @username sysname
DECLARE @objname sysname
DECLARE @found integer
DECLARE @sql nvarchar(4000)
DECLARE @results TABLE (Login sysname)
SET @username = ' '
WHILE @username IS NOT NULL
BEGIN
SELECT @username = MIN(name)
FROM master.dbo.syslogins WITH (NOLOCK)
WHERE sysadmin = 0
AND securityadmin = 0
AND serveradmin = 0
AND setupadmin = 0
AND processadmin = 0
AND diskadmin = 0
AND dbcreator = 0
AND bulkadmin = 0
AND name > @username
-- this is the list of non system logins
-- ids in server roles may not have corresponding users
-- any database but they should not be rremoved
SET @found = 0
IF @username IS NOT NULL
BEGIN
-- now we search through each non system database
-- to see if this login has database access
SET @objname = ''
WHILE @objname IS NOT NULL
BEGIN
SELECT @objname = MIN( name )
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE
-- name NOT IN ('master', 'model', 'msdb', 'tempdb')
-- AND
name > @objname
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
IF @objname IS NOT NULL
BEGIN
SET @sql = N'SELECT @found = COUNT(*) FROM [' + @objname
+ N'].dbo.sysusers s WITH (NOLOCK) JOIN master.dbo.syslogins x WITH (NOLOCK)
ON s.sid = x.sid WHERE hasdbaccess = 1 AND x.name = '''+ @username + ''''
EXEC sp_executesql @sql,N'@found Int OUTPUT',@found OUTPUT
--SELECT @found, @objname, @username
IF @found IS NOT NULL AND @found > 0
SET @objname = 'zzzzz' -- terminate as a corresponding user has been found
END
END
IF @found = 0
BEGIN
-- EXEC sp_droplogin @username
-- SELECT @username + ' was removed ' + CONVERT(varchar(23),getdate())
INSERT INTO @results
SELECT @username
END
END
END
SELECT Login
FROM @results r
ORDER BY Login
GO
--Test execution
EXEC rpt_security_Logins_with_no_permissions_2000
The SQL Guy 