The SQL Drive Space report incorporates a couple of interesting uses for report parameters and expressions to display a bar chart and table containing the drive space inormation. The High_Usage parameter lets users select all servers or only the servers that have a threshold event (which I arbitrarily set to a disk file usage above 30 percent). If you select the value High Usage from the parameter dropdown list, you’ll see only those servers that have potential drive space issues. Otherwise, you see all servers by default if you don’t select a value. As Figure 4 shows, color coding helps you determine at a glance which of the servers has a threshold value exceeded.
The SQL User Permission report has saved me hours of time investigating where access was granted—especially when a user was placed in a group and thereby hidden from the DBA. Even with access to Active Directory (AD), analyzing Windows domain group membership is tedious. Instead, I wanted one big result set that returned all the user information for all the SQL Server machines being reported on (especially production servers). The resultant query, which Web Listing 3 contains, and the subsequent report let you select a user by name or part of a name, as well as by group or by server.
The report, although not pretty, is quite useful. Web Figure 2 shows a report run for Location 4 (from the Location field), with a breakdown of permissions for each database. You can analyze Windows group and account names, although the report in Web Figure 2 doesn’t list any. Finally, the user parameter supports wildcard lookups. For example, you could look for a combination of all the servers in Location 4 and users with a name like “gue,” to return only the “guest” user. This option is handy when you know only part of a name.
Reader Kudos
Thanks to everyone who read my original articles and provided feedback to help improve my solution. I’ll continue to make enhancements to the tool (which I’ve dubbed SQL Snapper, for snapshot data analysis— plus, I live near the beach and I like to fish!). Although I created the tool for straight reporting, it’s useful for many other projects as well. Have fun tinkering with the code (in a test environment of course), and feel free to email me with any ideas you have for modifying or expanding it.