Understanding The Basics of SQL Server Security

Confusing

As I’ve worked with folks using other database engines, I’ve realized that Microsoft SQL Server has some terminology and handling that is a bit confusing. Here’s my attempt to clarify the basics for myself and others needing a quick overview. This is not comprehensive coverage of security architecture, which is a very complex topic, more just terminology.

Terminology

Note that it’s best to consider SQL Server as it’s own operating system, not just a standard application running. It has its own memory manage, cpu optimization, user security model, and more. It’s helpful in understanding why a Server Login != Instance Login by reviewing common terminology. I’ve noticed that among other open-source tools like MySQL, it’s much more common to hear terms like “Database Server”, which in my mind mix up for non-dbas the actual scope being talked about.

TermDefinition
ServerThe operating system
InstanceThe SQL Server Instance that can contain 1 or many databases
DatabaseThe database inside the instance.

This can be 1 or many.

TermDefinition
Server LoginWindows or Linux user at the Operating System level
SQL LoginLogin created inside SQL Server, using SQL statement. This is internal to SQL Server and not part of the Server OS.
Database UserA database user is created and linked to the Instance SQL Login
Server RoleRoles for Instance level permissions, such sysadmin (sa), SecurityAdmin, and others. These do not grant database-level permissions, other than sa having global rights.
Database RoleA defined role that grants read, write, or other permissions inside the database.

Here’s a quick visual I threw together to reinforce the concept.

Yes, I’m a talented comic artist and take commissions. 😀

https://d33wubrfki0l68.cloudfront.net/f69656ceaf9d6efb9ce2e7f29cbdf57925938a0c/bb630/images/2021-06-25-1658-sql-login-database-architecture-dark.png
Visualize SQL Security 101

Best Practice

When managing user permissions at a database level, it’s best to leverage Active Directory (AD) groups. Once this is done, you’d create roles. The members of those roles would be the AD Groups.

No Active Directory

SQL Logins and corresponding database users must be created if active directory groups aren’t being used.

Survey Said

I did a quick Twitter survey and validated that Active Directory Groups are definitely the most common way to manage.


Webmentions

(No webmentions yet.)