Creating a user that is not allowed to run select *

Question

Post reply

Creating a user that is not allowed to run select *

rynmthw3

SSC Enthusiast

Points: 114
More actions
January 10, 2013 at 10:46 am

#268611

Hello I am a SQL Server DBA and a project that I am looking into is to limit the user account for our analytics group due to them writing high cpu select * queries. I am wondering if this is possible in SQL Server 2008 R2, Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

rynmthw3 SSC Enthusiast Points: 114 More actions · Answer 1

I am looking to

1.Prevent ability to perform a select *, or if they are doing a select * the query must have a where clause

2.If the query runs longer than 120 seconds, automatically kill it

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

This sounds like a good use of Policy Based Management.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rynmthw3 SSC Enthusiast Points: 114 More actions · Answer 3

Would you know how to go about setting this up. I have been looking in the user policy for sometime now but havent found anything specific to revoke Select *

Lowell SSC Guru Points: 323518 More actions · Answer 4

i think there's a couple of factors here:

queries and indexes need to be optimized, and people having permissions to do ad hoc queries when they are not supposed to.

the right thing to do is to attack those two issues first, instead of preventing select *'s or high cost queries.

the query governor can be used to prevent high cost queries, but that's not an optimal solution, because there are always some processes that should be allowed to run longer queries.

i can't seem to find the thread where this was discussed in detail before;

i know there was one that was similar, where the OP wanted to prevent insert /update/delete without a WHERE statement, and at least one idea was the use of a trigger reading dbcc input buffer.

edit

http://www.sqlservercentral.com/blogs/sqlchicken/2012/07/27/how-to-prevent-select-the-evil-way/

http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/08/09/how-to-prevent-select-the-elegant-way/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rynmthw3 SSC Enthusiast Points: 114 More actions · Answer 5

Im thinking that the Resource Governor is a great Idea Im reading up on it now and it sounds like a great tool for what I am looking to do. Rather than revoking the rights to this user group limit the resources that they have availible so it isnt going to "jack" my server....

Any input on configuring Resource Governor or your thoughts on the tool are greatly appreciated!!

GSquared SSC Guru Points: 260824 More actions · Answer 6

Advice about it? Read the documentation and ask the questions that come to mind. I found it pretty straightforward once I just followed the directions on MSDN, the first time I used it.

I have a user who can do ad hoc queries on a group of multi-million-row tables. Poorly defined queries could definitely crush the server, so I set that user so a max percentage of CPU and RAM. Tested it by connecting as that user, then deliberately firing off a bunch of HUGE queries, in multiple connections. Server didn't hiccup, but the queries were really quite slow. Normal queries, well-written, ran just like usual with no performance impact on the user or the server. It's a thing of beauty in its own way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 7

i also remember that if you apply column level permissions to a table for user or group, and leave one of the columns /deny one of them, then select * gets disabled and returns an error as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Lowell (1/10/2013)
i also remember that if you apply column level permissions to a table for user or group, and leave one of the columns /deny one of them, then select * gets disabled and returns an error as well.

Yup - that is a trick that can be used. Very effective as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events