Limit the power of Query Analyzer?

Question

Post reply

Limit the power of Query Analyzer?

Jeff Steinweber

SSC Eights!

Points: 916
More actions
May 8, 2006 at 9:25 am

#114133

Is there a way to limit someone from being able to do UPDATE and DELETE statements from Query Analyzer so that they can only do SELECT statements?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Holger Steidel SSChasing Mays Points: 602 More actions · Answer 1

Best thing is to create a specific user account and set the right permissions.

regards,

Holger

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 2

Thanks Holger. I guessed at that option too but the wrinkle here is within Query Analyzer the user has the option to connect using their Window's Account or a SQL account. There is nothing to force them to always use the SQL login.

This user's Windows Account gives them permissions so that they can do the necessary database transactions via an application so I cannot take that away since they would still need to use it. I can create a SQL login for them to use but the door is still open for their Windows account.

Any other ideas or solutions?

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Why is the user using Query Analyzer? Do they have to be able to use it? Or is this just a case of "it's on their machine"? If that's the case, just uninstall QA. But bottom line is....if they have permission to do something from an application, what's the difference if they do it from the app or from Query Analyzer?

Unless I'm missing something here. You can change a user's permissions EVEN if they use Windows Authentication. It is still a login in SQL Server and has permissions assigned to it.

-SQLBill

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 4

Let me backup a little. The users connect to the database through an ODBC connection. (Not my design but a pre-purchased application that MUST have the ODBC connection to operate). All users are members of a group that has rights to the database. A trusted select few users have a need to gather their own data through adhoc queries and in most cases this would be answered by a report being developed to collect the data they need. There are already many reports developed for the typical user.

This particular user needs to run some scripts to be able to futher crunch some numbers that the reports are not doing for them. It would be helpful to allow this user to query directly and save some of their own scripts but still limit them from doing any damage.

I cannot change the user's permissions since it would affect all members of the group. The user is not an explict login.

All I really want to do is give the user the tool but limit its power.

Terry Seifert SSC Enthusiast Points: 178 More actions · Answer 5

Kind of a screwy way, but maybe you could set up a sql account with the correct permissions and force them to use that account in the menu when opening up analyzer (i.e. isqlw -S ServerNm -U UserNm -P Password). Just don't give them a menu to open it any other way. I know it's not perfect, but it's all I can think of.

andy hollis SSC Journeyman Points: 93 More actions · Answer 6

Probably an impossible suggestion due to the 3rd party application but just in case:

If all of the updates are performed through stored procedures you could revoke update and delete permissions from the base tables and grant execute on the sprocs.

That way you cannot update the tables using QA.

Jeff Steinweber SSC Eights! Points: 916 More actions · Answer 7

Andy I think you are on to something. All updates, deletes, inserts are done with SPs throught the application. It will take some testing to make sure it works. Thanks for the suggestion.

Robert Bedick Old Hand Points: 310 More actions · Answer 8

QueryToDoc (http://www.schematodoc.com), which exports query results to a Word or HTML document, restricts queries to Select statements.

andy hollis SSC Journeyman Points: 93 More actions · Answer 9

Beware of dynamic SQL as exec'ing a delete statement in a sproc for example will still require delete permissions on the base table.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 10

There is always a process way. Just to stress to the users that he/she should use only the approved login when connecting through Query Analyzer.

Also, most of the report tools access database read-only or I create read-only login for the report tool connection.

Regards,Yelena Varsha

vishy Mr or Mrs. 500 Points: 596 More actions · Answer 11

Did you try creating app role for the application alone to use? you can restrict access to everyone other than the application.

Vishy

mhempenstall SSC Veteran Points: 276 More actions · Answer 12

I agree with the above that the best way would be to force your users to use a particular account which does not have permission to update the tables.

However, if you can't then one thing you could try is to have a trigger on all of your tables and put this code at the top:

DECLARE @AppName varchar(8000)

select @AppName = program_name from master..sysprocesses where spid = @@spid

IF @AppName = 'SQL Query Analyzer'

BEGIN

RAISERROR ('Cannot perform updates via Query Analyser', 16, 1)

ROLLBACK

RETURN

END

You could also include Enterprise Manager if you want and record any inserts, updates or deletes in an audit trail (including the @AppName) to see how it goes.