Dear Developers, It's No Longer OK To Not Know The SQL Server Security Model

2015-05-14 0 Comments

I've got a bone to pick with you developers: stop asking for SQL logins with "sa permissions". You don't need that much authority. You may think you do, but you don't. I'm not going to try to teach you the SQL Server Security Model. The rest of the SQL community has handled that task quite nicely. 1 If you need a place to start, by all means, click away.

Maybe you think you're that "edge case" or you're in that 99th percentile of developers that really, truly does require elevated permissions. Sorry, but you are still wrong. I can tell when I start giving you some pushback. You can't answer any of my questions without sounding like Ted Stevens trying to explain Net Neutrality. I bring up the Principle of Least Privilege (POLP) and it's apparent you know it neither by name nor concept.

Providing the bare minimum of required permissions within a database that has hundreds or thousands of objects can be downright difficult. I get it. And I don't demand it from others. I'll work with you, but you've got to meet me in the middle. I have lines I won't allow you to cross:

  1. It should be obvious, but I'll state it anyway: don't ask for a login with role membership in SYSADMIN. You're never gonna get it. Seriously, don't ask.
  2. Don't ask for role membership in any of the other server-level roles. I'll give in on BULKADMIN (if you can explain or demonstrate why you need it), but that's it.
  3. For the database users 2 You do know the difference between a SQL login and a database user, right? , don't ask for role membership in any of these: db_owner, db_securityadmin, db_accessadmin, db_backupoperator.
  4. You don't get a free pass at installation time. I'm not giving you a "temp" login with elevated privileges. If you need to create a database, change database settings, change server settings, etc., put it in a script and be prepared to defend the actions it performs. I'll review the script and run it manually, thank you very much. Make sure your installation package is robust enough to work when the script is run prior to installation.