[RESOLVED] SQL Querying Tools - Recommendations?

[FunkyDexter]

I'm looking for a decent tool we can give to a few key users to allow them to design queries for themselves against our production database. It won't be feasible to get these guys actually writing sql but they'll understand the data-structure so we can probably get away with giving them a tool similar to the query designer that's in SQL Server Management Server. I'm not comfortable giving them management studio, though, and I don't want them to be able to update the tables or data in any way.

Can anyone offer up any recommendations? It needs to be:-
1. Easy to use
2. Secure (it would be nice if it connected as an application user to make it easy for us to control the security in the back end)
3. Able to separate update permissions from reads

[NeedSomeAnswers]

What about just using the Microsoft Query Wizard in Excel ?

It has a similar Query wizard to Access & Sql Server (its just an add-in in excel 2003 & below, included already in 2007). Users could select whatever data they wanted from tables and it will return it directly into Excel.

You could setup a datasource for the user to connect, so you can create a General 'Reports' user or such-like. This will allow you to Control Security through the SQL User account.

The user can potentially write SQL through the Query Wizard, and so could do Updates, but if you lock down the Users rights and only allow data reads it could work.

[FunkyDexter]

Hmm, that's not a bad idea.

I'm troubled that they'd still be able to run updates though. At the moment we allow no direct access to the tables and I'm aware that we're probably going to have to open up table access to support whatever tool we use. As this is something we're not in the habit of doing I can see it being something that someone cocks up at some point so I'd prefer to just lock down updates completely. I suspect that just about any reporting tool would have the functionality to update though (it's kinda a selling point)

[NeedSomeAnswers]

Yes but if you set-up the same datasource on each users PC and they use that to connect, you can ensure that they are using the same User account.

You could then alter the permissions of that user account not to allow Updates, Wouldn't that work ?

The main thing would be making sure they had no other username & password available to connect, as long as you force them to use your pre-defined datasource i would have thought it would work.

[FunkyDexter]

Can you completely turn off updates for a user acount? I know you can do it on a table by table basis but I'm not sure if you can do it globally. I'm at home now but I'll double check tomorrow. If you can do it globally it'll do fine.

[GaryMazzone]

I think there is a dataReader role in the database just assign the user to that role. Should be what you want (at least I think so).

[FunkyDexter]

Looking at it there's DataReader which will probably do and I can also use DenyDataWriter which I imagine is going to fit the bill as well. I'll have to have a play with some of the roles and make sure they'll do what I want.

Thanks to Both Of You