November 2006 - Posts

This is probably one of the most requested reports for Management Studio, a simple overview of the total size and used space for your database files like that available in Enterprise Manager for SQL 2000 via the Taskpad view. Whilst Management Studio does have a Disk Usage report at the database level, this is not without its issues (most of which are fixed in SP2 BTW where this report has been broken down into a number of smaller reports to avoid the overhead it used to have in prior releases) and most importantly doesn't really give you a clear overview IMHO like Taskpad used to in Enterprise Manager. Attached to this post is the EM Taskpad.rdl custom report which replicates the Taskpad view for Enterprise Manager. Install instructions are the same as in the previous post except that this report is aimed at the database object type on Object Explorer. Enjoy! You can see a screenshot of the report here

Update: I've updated the report definition in the post attachment. Thanks to Steve Kass for pointing out a slight issue with the query used to display the database properties. If a database was owned by a windows user which had server access via a group the join between sys.databases.owner_sid and sys.server_principals.sid would not produce any rows so the top section of the report would be blank. This has been changed to use suser_sname(owner_sid) which should work regardless.

Posted by sqldbatips | 9 comment(s)
Filed under:

In case you haven't visited my website (sqldbatips.com) I thought I'd highlight a couple of the free tools available that may be useful to you

SQL 2005 Service Manager

With the lack of an equivalent in SQL2005 for the SQL Server Service Manager that comes with SQL2000, I've decided to write one myself. It's behaviour should be familiar to you if you've used SQL2000

SQL 2005 Service Manager

Reporting Services Scripter

Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters.

Reporting Services Scripter
Posted by sqldbatips | with no comments
Filed under:

Get the real-world guidelines, expert tips, and rock-solid guidance to take your SQL Server implementation to the next level. Drawing on the extensive experience and expertise from respected developers and engineers at Microsoft as they walk you through the specifics on solving particularly difficult issues.

SQL Server Best Practices Website

Posted by sqldbatips | with no comments
Filed under:
David Litchfield from NGSSoftware has posted an extremely interesting whitepaper comparing the numbers of security flaws identified by external security researchers and subsequently fixed by Oracle and Microsoft in regard to their database products. The paper makes very positive reading for Microsoft and seems to validate the SDL (Security Development Lifecycle) approach taken after Slammer. There have been no security flaws reported by external researchers for SQL 2005!
Posted by sqldbatips | 1 comment(s)
Filed under:
If you are tempted to run a custom report in SSMS that you didn't write yourself then please do open it in BIDS (just rename the .rdlc extension to .rdl for the Nov CTP to make sure you can see the queries) or your favourite XML editor first and examine the queries it contains to make sure you understand what's being run and to check for any malicious content. You can add pretty much any valid TSQL you want to a query in a report (e.g. drop database, truncate table) so unless you're 100% sure you understand what's running then don't open it in SSMS.
Posted by sqldbatips | with no comments
Filed under:
Currently only locally accessible data is available in custom reports hosted in SSMS (by locally I mean the instance/database you are connected to in Object Explorer). I'd really like to be able to pull in data from other sources hosted on remote servers such as MOM 2005 or other monitoring sources to provide a rich reporting experience within SSMS allowing the ability to combine current data with historical/aggregated data. If this sounds like a feature that you would like to see then vote for my suggestion on connect.
Posted by sqldbatips | with no comments
Filed under:
Currently with regard to custom reports in SSMS there is no support for SQL 2000 instances (or even databases on a SQL 2005 instance in compatibility mode 80). Custom reports only support SQL 2005 databases in compatibility mode 90. Something to be aware of as I spent a fair while making sure my EM Taskpad report worked against both only to discover that it won't run at all against SQL 2000 databases
Posted by sqldbatips | 1 comment(s)
Filed under:

Once of the great new enhancements in Management Studio in SP2 is the ability for users to create their own reports. There's a nice write up with details of the builtin parameters (and some of the limitations) here. One of the views I miss most from Enterprise Manager is being able to select a database user or role and see at a glance all the permissions that they have.

SP2 has actually made some improvements in this area in that when you select a user or role it will now load up all the objects that have explicit permissions into the top window of the permissions dialog (previously you had to select them manually using a rather clumsy interface) however you still have to select an individual object to see all permissions (e.g. need to select a table to see if the user has just select permissions or insert/update/delete as well).

So the first report I thought of was a "matrix" of all objects and common permissions for a specific user or role that shoudl cover 99% of scenarios. This is attached to this post as Database Permissions.zip

To use the report, extract it somewhere locally on your PC (default location is My Documents>SQL Server Management Studio>Custom Reports) than in Management Studio, browse to the Security folder under a database and right click on a user or database role and choose Reports>Custom Reports. Select the Database Permissions.rdl file and it will run the report (say yes to the warning dialog). The good thing is that SSMS will remember which reports are associated with which object types so from now on the report will be available under Custom Reports without having to browse for it. Hope it helps! A screenshot of an example report can be seen here

Posted by sqldbatips | 10 comment(s)
Filed under:
Well I've finally conceded to peer pressure and started writing a blog. For info about me see the About section of the blog but in short my name is Jasper Smith, I'm a SQL Server MVP and I also run sqldbatips.com. Thanks to Tony for hosting and Simon for helping persuade me this was a good idea! Hopefully this will give me a chance to share some of my thoughts, tips and tricks that don't make it into full blown articles on my site and also share some of my experience of running SQL Server in an enterprise environment. Hope you enjoy it!
Posted by sqldbatips | 1 comment(s)
Filed under: