| Author |
Topic |
|
rav3n0u5
Yak Posting Veteran
88 Posts |
 Posted - 2006-03-14 : 21:06:53
|
| I have a rogue on staff who likes to hammer our SQL server whenever he feels like it. Unfortunately, this kind of hurts operations. I can't restrict him using security methods, because, well, he is my boss...Can anyone point me in the direction of something that might be able to limit the resources he can use so he doesn't bring the server to a crawl? I know that there is the query cost mechanism, but to my knowledge (which isn't much), it is not user-specific. Can it be?Any help is much appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-15 : 13:55:58
|
| If you can't restrict his security, then there's nothing you can do to restrict the amount of resources he consumes on the database server.Tara Kizeraka tduggan |
 |
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-15 : 14:08:53
|
| Hmm... Revising my previous statement... I can't reasonably restrict the tables and views so that he couldn't use them to query against. But if they happened to return less information for him, or gave up processing sooner, he wouldn't be able to argue.Does that make it any more feasible? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-15 : 14:13:19
|
| I don't understand what you mean. Does he have sysadmin or db_owner?Tara Kizeraka tduggan |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-15 : 14:25:42
|
| He doesn't have to be, so let's say no. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-15 : 14:28:25
|
| Then restrict his access via views. That way you'll be able to restrict the data. So let's say you have Table1 and you only want him to see maybe 1000 rows. Your view would be this:CREATE VIEW SomeViewNameASSELECT TOP 1000 Column1, Column2, Column3FROM Table1GONo matter what he does he'll be limited to 1000 rows. You could also use a WHERE clause to restrict data. So you'd only need to provide the permissions on the views and nothing else.Tara Kizeraka tduggan |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-15 : 14:31:54
|
| Can you restrict certain views to certain times? So, if I wanted a table/view available only between the hours of 2:00 PM and 8:00 PM? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-15 : 14:34:37
|
| You'd have to create a job to grant and revoke the access.Tara Kizeraka tduggan |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-15 : 14:39:31
|
| Hmm... I think I like that last idea. Thank you for all your help (again, and again, and again...)! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-15 : 16:25:01
|
Perhaps you could develop a "play" area for him. For example, load another database on another server with just the data he is interested in and let him access that. Or install a developer edition of SQL Server on his PC, and put it there.You could explain that you set it up just to benefit him, so that those annoying production processes don't slow his important work down.  CODO ERGO SUM |
|
|
|
|
Restricting user's processor/resources usage?