3

This is not a technical question. I would like to know the best practice to use in a place where devs want to have SA for everything.

The most recent event is:

Devs want to use BCP OUT to export data to a txt file really fast.

What should I think about it? Should I say "why you need this to be THIS fast?"

I'm sure there's a lot of alternatives to export data to .TXT file. they can be slower but it does the job.

We don't use Windows login here, only logins, so I can't create a proxy account (I'm reading something about this).

Note: They use Delphi to write application program code.

Improve this question
2
  • 2
    See this and this answer for using CLR as an alternative. This way, you can just grant execute on the SPs.
    – Kin Shah
    Commented Apr 17, 2018 at 15:57
  • 1
    What environment are they exporting from?
    – LowlyDBA - John M
    Commented Apr 17, 2018 at 16:07

1 Answer 1

Reset to default
7

There are two things at play here

  1. Data security
  2. Server health

For 1, management has to be okay with any developer walking out the door with whatever data they export to a text file. This might be sensitive PII, it might be protected IP, or just customer lists that a competitor would want.

For 2, any one of these developers that has sysadmin can change settings, shut down the server, take backups, run DBCC commands, and a whole bunch of other stuff. If management is going to let them do that, then management and the developers have to be on the on call rotation to fix things when a developer with sysadmin messes something up.

Also, xp_cmdshell doesn't just write files out, it can interact with the file system, executables, and nearly anything else you can dream of. You could use it to map a network drive to a DropBox folder and... Well, you get the idea.

BCP doesn't necessarily need xp_cmdshell to work. Maybe time to talk about the process behind that.

Permissions

A bcp out operation requires SELECT permission on the source table.

A bcp in operation minimally requires SELECT/INSERT permissions on the target table. In addition, ALTER TABLE permission is required if any of the following is true:

Hope this helps!

Improve this answer
2
  • 1
    For 2, if they are sysadmins, they can (and cannot be stopped) decrypt through powershell out every SQL Login and their passwords an on the Server (and the linked servers,too). Though, really, Devs shouldn’t be sysadmin anyways.
    – clifton_h
    Commented Apr 17, 2018 at 17:13
  • I told them to call DOS via delphi ( I found a topic about this, and it works ). I'm not going to give such powers to them. never. NEVER !!!!
    – Racer SQL
    Commented Apr 17, 2018 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.