By now, you probably know that in SQL Server 2005, a new tool that replaces SQL Server Enterprise Manager and SQL Server Query Analyzer is SQL Server Management Studio (SSMS). It has a lot of good stuff in it, one of them being the reports.

SSMS reports provide a snapshot of your server status and database status, like cpu, memory, io, cursor usage, schema changes, index usage, user statistics, etc. All these are great information available at your fingertips. MVP and PASS director Randy Dyess has a great intro post on them recently.

When I saw the demos at PASS of those reports, I was really impressed and excited about them. They definitely provide the DBAs with tons of information to proactively monitor and manage db server and databases. One thing bugged me a little during the sessions is execution permission on those reports. I could be wrong, but I believe most of the data for those reports come from DMVs or SQL Server 2005’s internal resource database. So, if you make SSMS available to developers or some regular database users, the reports will be available to them, but they cannot use them, unless they have system privileges.

To prove my theory, I created a login for AdventureWorks, a replacement for Northwind and Pubs. I gave this login db_reader and db_writer rights, a common practice for a regular user. I then logged into this database with this login, using SSMS. Sure enough, under this regular user’s login, when I clicked on any server reports, I got a permission error. I haven’t checked database status reports yet, because I’ve been playing with silent / command line install and just removed all my installations. I will test that when I get it installed again.

I think this opens a can of worms for your average DBA. Since SSMS is the only client tool available for SQL Server 2005, you don’t have a choice of whether to install this for your developers and average users. (We will have to see if Query Analyzer works with SQL Server 2005, if yes, install that for them, but that is really lame). It’s like showing a candy jar full of goodies to your child but won’t allow him to open it. The average user will constantly bug you on this and make you feel denfensive and waste your valuable time while doing it.

I then thought, geez, maybe the available reports are stored in the registry or a configuration file. If that is the case, I can then delete them after SSMS is installed on a client machine. So I did a string search in the registry, nothing came up. I then did another string search using MSN tool bar on the hard drive. Nothing came up either.

I am all for transparency and information sharing, but it seems to me that if you don’t want people to use certain feature, don’t let them know they exist in the first place. It will be great if the available reports are configurable.

What do you think?