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.
End of Article
Prev. page
1
2
[3]
next page -->
