The Principle of Least Privilege

One of the tenets of good security is that no person or process is granted more rights than it needs to accomplish a task. This is the Principle of Least Privilege, and is designed to ensure that your system is as secure, stable, and predictable as possible.

For a SQL Server instance, this means that unless a person or process needs to perform instance level changes (configuration, patching, etc.), then they should not have instance level privileges. If a process accesses data in a database, there is no need to grant sysadmin privileges, even if they do need db_owner privileges.

Many software packages require sysadmin access be granted, often without any justification. Developers build software as a sysadmin on their own workstation, or on a development server. They often don’t understand or realize that less privileges will still allow their software to work. As a result we find software running under elevated privileges, which can be a security risk if there are bugs or injection holes in the software.

It’s hard to make changes later on, and it can be frustrating to argue with vendors that their software doesn’t need elevated privileges. The best way to combat this is to educate yourself and any developers you know about security.

Adding login or user accounts does not require sa privileges. The securityadmin and db_securityadmin roles can be used. The rights to create objects in a database can be handled with granular permissions in a schema, or with the db_ddladmin role instead of db_owner.

Work with limited rights when you can, and help other learn how to work with the minimum they need and we’ll all be more secure.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

6 Responses to The Principle of Least Privilege

  1. thomasrushton says:

    I’ve seen (and blogged about) worse than that – one software system I was looking into a couple of years ago not only demanded sysadmin privs, but demanded the sa password…


  2. way0utwest says:

    I hate that. I know a few packages that have asked for that, but most of the time when I’ve traced the actions, they were just using SA to create objects (usually logins or databases). Once you explain they don’t need those privileges, I’ve had success with software vendors.


  3. John Rogerson says:

    The law of self-preservation applies too. As a SQL BI developer I usually need db_datareader access to production databases to service ad-hoc user queries. When called on for support when production changes may be involved I request my second login is activated (an audited event – for some reason well beyond my limited comprehension DBAs seem to be a little sensitive about developers footling around in production) – this second login has the necessary elevated rights. With this separation of permissions, accidental “damage” to production is avoided during normal development activity when I may forget which server I am connected to.


    • way0utwest says:

      I wouldn’t grant you db_Datareader. I’d make a role for BI developers, add all tables, and add you. I can add new tables as I add them, but if I add something you don’t need (metrics, tracking, etc.) I don’t have you with rights there. They also don’t appear in any tool you use.

      I’ve had a “tech support” login/user in databases before, often for vendors, that I disabled when it wasn’t being used, and I (or another DBA) wasn’t around to watch. The reason we don’t want developers mucking around is that you might do something and we would not be aware. If you aren’t primarily responsible for production, it’s easy to think a chance or query won’t cause an issue, don’t log it somewhere, and it does cause a problem. It’s less worry you’ll make a mistake, since production DBAs make mistakes, but more that we won’t be aware of an action and it might be logged.

      It’s hard enough getting production people into this habit. Worse with developers, mostly because your habit is to try things in development and see if they work.


  4. SQLMechanic says:

    A policy that any third party application that required instance level permissions or permissions their technical rep couldn’t justify required a stand alone server for the sql instance added cost (server, sql licence, etc) to procurement and was an effective way to make the unnecessary risks visible to the business in a concrete way. It was an effective tool in both getting vendors to work with us to limit the permissions to the necessary and/or get the business to reconsider other vendors.


    • way0utwest says:

      Less of an argument with virtualization. I’ve usually just required that the vendor justify things. Often they can, because they used sh***y developers that just wanted things to work. However tracing the actual application often shows that sysadmin, or even db_owner, isn’t required. If it is, these days I’d make a role, and add the user with the limited permissions they need, even if that’s something like backup, create, etc.


Comments are closed.