Whether in Microsoft SQL Server or any other database environment, giving too many and too high permissions can pose security risks. The more people have all rights, the greater the risk becomes and the greater the chance that you will lose grip and control of your IT environment. Mark van der Haar describes a number of real-life situations in this blog.
Least Privilege Principle
The Least Privilege Principle refers to an information security concept in which users are given the minimum privileges they need to perform their tasks, but certainly no more. The demand for administrator privileges is quickly made, but rarely is it actually needed. In fact, it is only needed when someone needs to modify configuration parameters. But that role is not for everyone. Before you know it, someone is viewing the password hashes, copying them to another server and cracking them there with brute force. But to create and modify jobs, you need those permissions again.
An application administrator with sysadmin privileges
Usually, administrators only need db_datareader rights - or whatever you call read rights in your database environment - on the user databases they manage. If you want to include future databases right away as well, SQL Server allows you to include that role in the model database. Because auditors want to know what is changed in the database outside the application (for example, due to SOX), then db_datawriter is already not allowed. If the application manager also needs to monitor jobs, then the role can be extended with the msdb role SQLAgentOperatorRole. Are SSIS packages also running? Then the rights can be extended with the SSISDB role ssis_admin.
An account to deploy changes with sysadmin privileges via a pipeline
The danger here is that through such a pipeline you can also create many more logins with sysadmin privileges. A developer once told me that this is the only way Azure's DevOps pipeline could work and that even Microsoft pointed this out. The challenge, of course, is to find that information on a Microsoft site. Not easy, but I managed to find the right info! Microsoft indicates that you should give the login the dbcreator-server role, as well as the ALTER ANY LOGIN right. Make the login also db_owner in the model database and the login can also maintain the databases once created.
A data engineer with sysadmin rights
It depends on whether it is a database to be read from (db_datareader will do) or a database to be written to. On the database to be written to, you need at least db_datawriter. If structural changes are also made to the database, the data engineer must be db_owner on this database. If the data engineer also needs to create ad hoc databases, then he should be given the dbcreator-server role and the db_owner role on the model database. Data engineers often use temporary tables, but no additional permissions are needed for that.
A developer with sysadmin privileges in production
Automatic deployments are widely used nowadays, and then there is no need to give a developer's account many rights. Then it's more about rights like VIEW ANY DEFINITION. If this is not yet being used, then it is only necessary to give the developer the same rights as a deploy account in the development environment and possibly the test environment. In the rest of the OTAP street, changes are made by a DBA who gets the scripts, and preferably via a ticket system. In the ticket system, approvals from testers and owners can then be administered immediately.
Creating an application account owner - or sysadmin - of the user database
An application account may already have enough with the db_datareader and the db_datawriter role. Or even finer, create stored procedures for all actions and only hand out EXECUTE permissions on the schema. You often see an application account given db_owner rights, but is it really necessary for an application account to have the right to backup, or worse drop the database? Giving db_owner rights by making the application account owner of the database is dangerous. Should a restore be necessary, the login that restored the database becomes owner and the previous owner loses his rights.
Extensions to the public role
Each login and user is assigned the public role. If you grant additional rights to the public role then it will apply to everyone. Sometimes this is useful, if a particular right should indeed be distributed to everyone. But during the lifecycle of a server there are extensions to functionality and more logins are added. It does not at all mean that the new logins should also be given this right. Therefore, best practice is to leave the public role default.
Include the local administrator account or administrator group as a login and even make it sysadmin
Often these are system administrators who sometimes need to be able to do some things within SQL. I would use a different account for this than the system administrator account. An account that has many rights both at the Windows level and at the SQL Server level is a true super-user, though. Such an account hackers would love to have to collect their ransom. Also, vice versa, the engine's service account is included in the local administrator group. Also a dangerous combination.
Giving the account used for the monitoring tool sysadmin privileges
Often this has come because not every tool vendor is equally clear about what the permissions should be. And then it is decided, after some failed attempts, to just give sysadmin. A dangerous situation because the monitoring tool often has to monitor the entire server-park, or at least the production servers. And sometimes here only VIEW SERVER STATE is sufficient. And if more permissions are needed, they are read permissions (on the DMVs e.g.), certainly not sysadmin.
Summary
Of course, all these examples are broadly described and everything can be fine-tuned even better. But assigning sysadmin because it is not immediately clear what the needs are, I still see that happening regularly. At the same time, reversing sysadmin rights often meets with a lot of resistance. The motto is: Think before you start.
Would you like advice or a review?
Have you also assigned too many sysadmin rights in your environment and would like to take a critical look? Do you need objective advice or help? Please feel free to contact us, we would be glad to help you!