SQL Server security made easy on the server level

Today, we’re going to look at the options we have for server level security. In SQL Server we can add security on many different levels. We are not going to discus anything outside of the SQL Server like the VM or server itself or firewall etc, this will bring us too far from the subject.

In SQL Server we have the following levels of security:

• Instance
• Contained databases
• Databases
• Schemas
• Objects (Like tables, views,…)
• Columns
• Rows

We will discuss the first two levels of security – instance and contained databases – in detail in this blog post. The other levels will be discussed in one of the following blogs. So, stay tuned for those!

To log into our SQL Server you need to have a login defined on your instance. This can either be a Windows User, Windows Group or a SQL login.

Windows Users and Groups are defined in the Active Directory of our environment. These are mainly managed by the system administrators of the environment and can be used for many different things.

SQL logins are defined in SQL Server itself. These logins can’t be used outside of SQL Server, as these are unknown to the rest of the environment.

This is also where the difference between them comes in. The SQL logins are fully managed by SQL Server itself. Windows Users and Groups are managed in Active Directory.

Picture 1: Overview SQL Instance security

What’s the best option?

It depends! But in general, we prefer the use of Windows Groups for SQL security. This allows us to give the same permissions to groups of people and easily manage this. When we use Windows Users or SQL logins, we would have to give every user their permissions separately. For small environments this might be doable, but once your environment grows it will become a nightmare to manage. So, even if your environment is currently still small, try to use AD groups as much as possible.

Keep in mind that it’s certainly not bad to sometimes add a SQL login or AD User to give permissions. If you need to give specific permissions to a user or a service account and you’re certain that nobody else will ever need the same permissions as this person, it’s perfectly fine to give specific permissions to their login.

Server roles

We now have our logins defined and people are able to login on the server, but they can’t do anything yet. How to give permissions on databases to our logins will be discussed in a future blog. We do have a couple of permissions we can give on a server level. Some of these permissions are contained in server roles that we can give to our logins. I’ll highlight a few of the most used ones, but you can find a detailed explanation for all of them under this link.

Sysadmin

Arguably the most important role we have on the server. This role means you can do everything. This role is only for your dba or dba-like people in your environment. Nobody else should need this role.

Securityadmin

Technically this role is the same as Sysadmin or should be treated the same. Logins with this role can only change permissions, so at first glance you’d think it’s not the same as Sysadmin. But being able to change permissions, also means that they can promote themselves to sysadmin. So certainly be careful handing this permission out.

Public

This role is given to every single login by default. It only has a few basic permissions by default, however, you could change these. We do not recommend granting or denying permissions through public, rather create a separate role for this.

Since SQL 2022 we have a more extensive list of roles we can give. You can also find these in the link above with an explanation.

Server Permissions

Besides roles we also have separate permissions in SQL server. We can either create our own role with permissions or give permissions directly to logins. When creating a role you can see a list of all the possible permissions we’re able to give. As an example you can see permissions we can add on server level to our role below. A complete list of all permissions can also be found on the Microsoft Docs.

Contained databases

Contained databases work a little different. Instead of letting the SQL Server handle the security, all security is handled within the database itself.

A big advantage of contained databases is that everything is contained within the database itself. If you restore a normal database on another server, you also have to move logins etc related to the database. When moving a contained database, all logins are transferred with the database itself.

Database users

We now have our login and we can give some permission on server level, but we still can’t access our database. This is where database users come in. In picture 1 you can see that we have to map our server login to a database user. We can then grant this database user certain permissions on the database itself. As a general rule, keep the name of the database user the same as the name of the login.

Most important rule you should take away from this blog, is try to work with AD groups as much as possible. In our next blog, we will dive deeper into database security and everything related to that topic. Stay tuned for that!