Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Allowing BULK INSERT for non-SysAdmin Users in SQL 7.0 Expand / Collapse
Author
Message
Posted Sunday, October 28, 2001 12:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, 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
Post #1444
Posted Monday, October 29, 2001 10:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 9:03 AM
Points: 171, 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.




Post #23084
Posted Tuesday, October 30, 2001 3:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, 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
Post #23085
Posted Tuesday, October 30, 2001 3:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 9:03 AM
Points: 171, 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.




Post #23086
Posted Wednesday, October 31, 2001 2:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, 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
Post #23087
Posted Wednesday, October 31, 2001 4:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:08 PM
Points: 6,790, Visits: 1,904
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
Post #23088
Posted Wednesday, October 31, 2001 11:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #23089
Posted Wednesday, October 31, 2001 5:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 9:03 AM
Points: 171, 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.






Post #23090
Posted Thursday, November 1, 2001 2:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, 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
Post #23091
Posted Thursday, November 1, 2001 10:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
Maybe its even a little "job security". Don't make too many notes, Paul! :)



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
Post #23092
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse