SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Allowing BULK INSERT for non-SysAdmin Users in SQL 7.0


Allowing BULK INSERT for non-SysAdmin Users in SQL 7.0

Author
Message
Paul Ibison
Paul Ibison
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 32
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
jamyer
jamyer
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 16
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.



Paul Ibison
Paul Ibison
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 32
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
jamyer
jamyer
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 16
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.



Paul Ibison
Paul Ibison
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 32
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
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11355 Visits: 2730
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61415 Visits: 19097
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
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jamyer
jamyer
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 16
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.



Paul Ibison
Paul Ibison
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 32
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61415 Visits: 19097
Maybe its even a little "job security". Don't make too many notes, Paul! Smile



Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search