SQL: Stop connecting applications as dbo or sa

SQL: Stop connecting applications as dbo or sa

I can’t tell you how many sessions for developers that I have attended, where the demonstration shows an application connecting to a database as the database owner (dbo) or as a system administrator (like sa). Are we then surprised when developers do this in their own applications?

Please don’t do this.

If you’re running a utility application that’s only used by administrators, and is run within a secure part of the environment, that might be ok. But for normal user applications, it’s just not safe to do so, and it’s really poor practice.

But I need to do admin tasks!

Do you really?

One reason that I often hear for applications having high-level access like this, is that the application is used by end-users for their work, but also for administrators to do their work.

If so, that’s your first design mistake.

A similar reason is given for applications that have the ability to apply version updates as well as perform end-user work. Again, that’s a design mistake.

You need to keep administrative tasks and/or code that does version upgrades, in a separate application. It should be launched using short-lived administrative privileges by someone who can use those privileges. Having an end-user application connecting as an owner or administrator just because it might occasionally need to perform one of these tasks isn’t sensible.

Minimizing your exposure (and finger-pointing)

I’ve helped a number of organizations recover their systems after intrusions have taken place. You need to avoid being one of these organizations.

In almost every case, what has happened was an application was breached and then used to attack a database. The databases never seem to be attacked directly unless someone has done something truly silly.

When (not if) a breach occurs, the most important thing you need to already have in place, is a way of restricting the damage, and invariably, the finger-pointing that will follow. If someone gains control of your application, you don’t want them to have an owner or administrator level connection to your database to play around with.

The way I like to see this done is:

  • Separate out any code that requires administrative access, into a separate application, and one that requires privileged access while that administrative application is being used. This should be for code that doesn’t need to be run regularly.
  • Connect your normal application to the database using an app user that is a standard non-privileged user connection.
  • Grant the app user the minimum permissions required for the app to function. Generally, our preference for doing this is to have a separate schema (like WebApp) that contains code that the app user can execute (e.g., procs, functions, and views), and grant that user SELECT and EXECUTE permission on that schema. Don’t give the user any other access to the database.

I understand that doing this is a little harder, but when you have an issue, you’ll be glad you did.

2026-01-05