The Principle of Least Privilege was first mentioned in a publication by Jerome Salzer. (Saltzer, Jerome H. (1974). "Protection and the control of information sharing in multics").
In a system that has been developed in accordance with this principle, every user has only just those permissions granted that are needed to execute the day to day tasks. With that, if an account should be compromised, the amount of damage that can be done is minimized.
Because of this it is a generally accepted security best practice to always follow the Principle of Least Privilege when designing any IT solution.
Let’s look at a brief example: Your boss likes to have the ability to run ad-hoc reports in the main database. For that he needs to have read access to most of the data in your database. However, there is no need for him to be able to decrypt the encrypted credit card numbers and there is also no need for him to be able to update the data when he is only interested in running those reports. (We are not looking at the performance implications of having an unexperienced individual running ad-hoc reports throughout the day. That is another discussion in itself.)
If your boss’s password gets compromised, e.g. because he also used it on some other website, an attacker will be able to look at all the data, which is certainly not a situation you want to be in. But if the Principle of Least Privilege was followed, at least they won’t be able to alter the data or decrypt the stored credit cards. Imagine the damage that could have been caused if those precious numbers fall in the wrong hands.
But not only special accounts like the one in the example above should follow this principle. All accounts need to be looked at. Particularly important is the account that the application is using to connect to SQL Server.
Security comes always at a cost and it is no always easy to convince users that they really do not need this or that type of access. It also takes additional time to figure out what the minimal permissions are. It is admittedly a lot simpler to just add the account to the db_owner database role or even the sysadmin fixed server role. But you should invest that extra effort. No system is completely secure and if a breach happens, the damage to your company’s reputation and or financial assets will be much smaller if the Principle of Least Privilege was followed strictly.