asked
Meet Bill. He's from XYZ Consulting and he's an analyst.
So begins the introduction from the Exec Director. She or he then goes on to say "Bill will be with us for a while and he's going to need access to the data." "Okaaaaay... er... what data, exactly?" You ask with not a little trepidation, fearing the answer you know you'll get. "Well, all of it. Whatever he needs. There's a good lad. Chop chop." "...but..." "Tut! Just make it happen." One of your databases doesn't yet have an ETL process set up. It should. It doesn't. So in order to appease the Exec Director you stupidly give Bill data reader access to the production database. Sure enough after a few days users are complaining that performance is going through the floor. Obviously, being an analyst from XYZ Consulting, Bill is writing queries like SELECT * FROM biggest_table_i_can_find (and then doing his joins using vlookups in Excel) several times a day. You know that revoking his data reader access will result in a résumé-generating outcome for you. You don't have the time or staff to develop an ETL process for him on the spot. Are there any "per user" settings that you can implement that will limit the damage Bill can do? Can you limit the time of day/day of week the User's login can access the database? Can you place a timeout on all queries by that user? Or do you just have to bite the bullet and put all your other work to one side, and draw up some specs and build the damn ETL?
Sounds like a potential CLM (Career-Limiting Move) whichever way you go. The data protection issues alone are a nightmare!
You may want to put in place some audit to log what 'Bill' is looking at (queries he is running), in case of later discovery of a data breach. Colleagues in the Information Security team (or Information Governance) may also need to have input on such an audit.
Yeah. Luckily the scenario doesn't apply to me personally. Some DBAs I know, though, have developed a kind of learned helplessness where if the Exec Director asks for it, it has to be done. Not a day goes by when I don't cringe at some of the appeasement compromises I see.
Whilst I would want to test it in a non-production environment first, I would look at using Resource Governor [(MSDN)][1] to allocate 'Bill' a workload group of his own (or perhaps create one for all large query users), assign his connections to that workload group, then limit the pool to which it belongs to achieve the required balance. There's some more examples [here][2] which cover the time possibilities you asked about. I'd probably make Bill aware of what I was doing, and the Exec director, because he will notice the effect, but this is probably a good middle ground compromise to achieve data access without crippling line of business data access. Alternatively - negotiate with Bill. Ask if he needs today's data, or would a restored backup on a different server do just as well for him? He may be more understanding than your Exec... And it would test your backups too. [1]:
https://msdn.microsoft.com/en-us/library/bb933866.aspx [2]:
https://msdn.microsoft.com/en-us/library/cc645892.aspx
17 People are following this question.
