October 28, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/pibison/bulkinsertworkaround.asp
Paul Ibison
Paul.Ibison@replicationanswers.com
October 29, 2001 at 10:54 am
An alternative to the author's suggestion would be to have the production job owned by a member of the sysadmin group such as a production DBA. The advantages are that you do not have to open a security hole to make a job work and that the developer can not change the job without going through the production DBA team. Some developers would consider this to be a disadvantage, but it does maintain the classic division between production and development that you should strive for in a medium to larger ADP environment.
October 30, 2001 at 3:30 am
The article really applies to those circumstances where someone - a developer - needs bulk insert rights, but you don't want him to become an administrator (sorry - but that was the title of the topic!). In SQL 2000 MS have recognised that this is a useful scenario and have added a Fixed Server Role for this purpose, but in SQL 7.0 my workaround is possibly the only solution.
Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com
October 30, 2001 at 3:38 pm
I understand your point. Now just because something is possible, doesn't mean it should be done. I was pointing out that from a production dba point of view you may not want to allow developers this type of access and I suggested a workaround that I have seen deployed at multiple locations.
October 31, 2001 at 2:37 am
Granted that if you have a choice then your solution would be preferable, but the article was intended to refer to those cases where an application uses a login, and this login may need bulk insert rights. Giving the login sys admin rights in this case is not desirable because then the development group will have knowledge to use (and abuse!)it.
Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com
October 31, 2001 at 4:20 am
Paul, I think I see your point - where I work the lines blur a little too, and it wouldnt be out of the question for me to need to give a developer this kind of access. The alternative might be for me to do it, but then I'm the bottleneck.
Jamyer, I don't disagree that this might not qualify as a "best practice", but I'd say based on the changes in SQL2K MS must have run into a lot of people that needed security to be a bit more granular?
Sometimes you just need a solution. For example, where I work I needed to have 2 people from a different part of our IS team be able to create databases on two servers, enable replication, and a few other things. It ended up that to really get it done right without me having to go behind, they needed SA access. Not a great plan - I trust them not to touch things they aren't supposed to, but the fact is with that kind of access mistakes get costly. The alternative I ended up fielding was an app that would connect as SA and do the tasks - they only get that access via the app. Could I have done this with a job? Maybe. Honesty didnt try since I needed an interface anyway.
My point is that in some cases, we have to step out of the box. Because of SQL limits. Business requirements. Politics even. As long as we KNOW the risks and do what we can to reduce them, you do what you have to do.
One of the great things about discussions like these is when a junior DBA or developer stumbles across it, they can find the dissenting opinions close by...giving a valuable perspective I think.
Andy
October 31, 2001 at 11:01 am
I agree with Andy. I think that this does have a place in meeting business rules or needs. There are times where I would like to allow someone to bulk insert into a table, from which I would have processes that validate, scrub, clean, etc. the data.
Steve Jones
October 31, 2001 at 5:13 pm
Context is important. The benefits of standardization and defined processes for your production boxes will help you sleep at night!
Now my general context is large shared SQL servers (many databases, many projects) within a corporate intranet. My experience in this context is that leveraging obscure features of SQL can lead to support problems down the road. (We have quite a bit of turn over among our developers.) Such as when person X leaves the company you get problems with objects tied to their NT account.
I don't consider myself to be an idealist, but I believe that context is important. And in general you keep developers off production systems where and when you can. Of course it is important to be practical and reasonable if you want to last in any environment.
Another way to say all this is KISS really pays off.
November 1, 2001 at 2:23 am
When I implemented this solution at my current company I decided that I would replace it as soon as we upgrade to SQL 2000, for the support reasons you mentioned. However perhaps my environment is a little different: large corporate lan, but only two concurrent projects. As I am the DBA (and hopefully will remain so!) for the forseeable future, this is not such a big deal - as long as I can remember what I've done!
Paul Ibison PhD, MCSD
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com
November 1, 2001 at 10:29 am
Maybe its even a little "job security". Don't make too many notes, Paul! 🙂
Steve Jones
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply