Narcis Miclaus

7 min read

SQL Server, from the ground up Lesson 33 / 40

Security basics: logins, users, roles

Principals, schemas, the public role trap, least-privilege. Plus GDPR-specific notes for an EU shop.

Security in SQL Server is a full book. This lesson is the one-chapter summary every developer and junior DBA needs to know: how authentication works, how permissions are organized, what the public role is and why it’s a trap, and the GDPR-flavoured considerations for an EU company like Runehold.

The principal ladder

SQL Server has two levels of “who are you”:

  • Server principals (a.k.a. logins) — authenticate to the SQL Server instance. Windows accounts, Azure AD accounts, or SQL-authenticated usernames.
  • Database principals (a.k.a. users) — exist inside one database, mapped to a login.

You can’t connect to a database without a login (instance-level) that maps to a user (database-level) there. Think of it as “your hotel keycard lets you into the building (login), and separately into your room (user).”

Login types

-- Windows login (domain or local user)
CREATE LOGIN [RUNEHOLD\anne.devries] FROM WINDOWS;

-- SQL-authenticated login (username + password)
CREATE LOGIN app_user WITH PASSWORD = 'SecurePa$$w0rd!',
    CHECK_POLICY = ON,        -- enforce Windows password policy
    CHECK_EXPIRATION = ON;    -- expire per policy

-- Azure AD login (on Azure SQL Database / MI)
CREATE LOGIN [anne@runehold.com] FROM EXTERNAL PROVIDER;

Windows/Azure AD logins are preferred. They inherit your org’s password policy, MFA, disabling, auditing. SQL-authenticated logins are quick to set up and a maintenance headache — don’t expire without external mechanisms, password rotation is a chore, etc.

Users and the mapping

USE Runehold;

-- Create a user mapped to a login
CREATE USER anne_devries FOR LOGIN [RUNEHOLD\anne.devries];

-- Create an "orphaned" user (rare, not mapped to a login — disabled by default)
CREATE USER some_user WITHOUT LOGIN;

The user name inside the database doesn’t have to match the login’s name, though it usually does for clarity.

Schemas and default schemas

Every user has a default schema — the schema SQL Server looks in when you write SELECT * FROM Customer without a schema prefix.

CREATE USER anne_devries FOR LOGIN [RUNEHOLD\anne.devries]
    WITH DEFAULT_SCHEMA = Sales;

Now anne_devries writing SELECT * FROM Customer finds Sales.Customer. Without the default, it’d look in dbo.Customer, not find it, and fail.

For application service accounts, dbo as the default schema is fine. For humans, setting it to the schema they work in most is a nice quality-of-life improvement.

Server and database roles

Roles bundle permissions so you grant “role” not “28 individual permissions.”

Fixed server roles

  • sysadmin — god. Can do anything. Grant sparingly, ideally to nobody except break-glass DBAs.
  • serveradmin — most server-level stuff.
  • securityadmin — manage logins and permissions (but can escalate themselves; as dangerous as sysadmin).
  • dbcreator — create and restore databases.
  • processadmin — KILL sessions.
  • public — every login is in it, can’t remove. Acts as the “default permissions” bucket.

Use them sparingly. For developers: usually nothing at the server level (or dbcreator for local dev). For service accounts: nothing at the server level unless genuinely needed.

Fixed database roles

  • db_owner — god within the database.
  • db_datareader — SELECT on everything.
  • db_datawriter — INSERT/UPDATE/DELETE on everything.
  • db_ddladmin — run DDL.
  • db_backupoperator — BACKUP DATABASE / LOG.
  • db_securityadmin — manage permissions in the DB.
  • public — again, the default bucket.

Custom roles

Good practice:

USE Runehold;

-- Create a role for read-only reporting users
CREATE ROLE ReadOnly_Reporting;
GRANT SELECT ON SCHEMA::Reporting TO ReadOnly_Reporting;
GRANT SELECT ON SCHEMA::Sales      TO ReadOnly_Reporting;

-- Add a user
ALTER ROLE ReadOnly_Reporting ADD MEMBER anne_devries;

-- Create a role for the application
CREATE ROLE App_Runehold;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales      TO App_Runehold;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Catalog    TO App_Runehold;
GRANT EXECUTE                         ON SCHEMA::Sales    TO App_Runehold;
ALTER ROLE App_Runehold ADD MEMBER app_user;

Custom roles grouped around real-world team/app roles keep permissions manageable. Never grant directly to users; always via roles. Even if it’s a role with one member today.

The public role trap

Every user is automatically a member of public in master and in every user database. It’s the “default” bucket.

Problem: public in master has a surprising default grant: GUEST used to have access to guest database, and public has access to many metadata views, plus some catalog views. A hostile internal user with nothing explicitly granted can still enumerate databases, some object names, and service-level metadata.

In high-security shops, you harden public by revoking everything granted to it in master. Be careful; some applications depend on metadata access. Test in a staging environment first.

For most shops the rule is simpler: don’t grant anything to public on user databases. Any permission you’d grant to public is probably better granted to a specific role.

Permissions: GRANT, DENY, REVOKE

-- Grant a permission
GRANT SELECT ON SCHEMA::Sales TO ReadOnly_Reporting;

-- Revoke (remove a specific grant, not an overall deny)
REVOKE SELECT ON SCHEMA::Sales TO ReadOnly_Reporting;

-- Deny (explicitly block, overrides any grant)
DENY SELECT ON Sales.Customer TO some_role;
  • GRANT adds permission.
  • REVOKE removes a specific grant.
  • DENY explicitly blocks, and beats any grant. A user with SELECT granted via role membership and DENY on the same object gets DENY.

Use DENY sparingly — it’s effective but confusing. Most access patterns should be built with GRANT alone.

Least privilege

Rule: every account has exactly the permissions it needs to do its job. Not more.

Application service accounts:

  • A role that can read, insert, update, delete in its own schema(s).
  • EXECUTE on its own stored procedures.
  • No DDL. No db_owner. No db_ddladmin.

Human accounts:

  • Read access to what they need to see.
  • Execute on report procedures.
  • Strictly controlled write access if needed.

DBAs:

  • db_owner on their databases. sysadmin for one or two people who need it. Everyone else gets less.

Audit this periodically. Query what permissions each user has:

SELECT
    pr.name                                             AS principal_name,
    pr.type_desc                                        AS principal_type,
    p.class_desc,
    OBJECT_SCHEMA_NAME(p.major_id) + '.' + OBJECT_NAME(p.major_id) AS object_name,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions  AS p
JOIN sys.database_principals   AS pr ON pr.principal_id = p.grantee_principal_id
WHERE pr.type NOT IN ('R', 'A')     -- skip roles and application roles for clarity
ORDER BY pr.name, p.class_desc;

Run this once a quarter. Remove anything unexpected.

Contained databases

A newer option (since SQL Server 2012) is contained databases — databases whose users exist entirely inside them, not mapped to server-level logins. Useful for Azure SQL Database (which is inherently contained) and for portability: you can move a contained database to another server without recreating logins.

Syntax:

-- Requires sp_configure 'contained database authentication' = 1 at server level
ALTER DATABASE Runehold SET CONTAINMENT = PARTIAL;

USE Runehold;
CREATE USER app_user WITH PASSWORD = 'SecurePa$$w0rd!';

No login at the server level. User authenticates directly to the database.

Nice for isolation. Less mature tooling around auditing. Pick based on your monitoring / governance tools.

GDPR-specific notes

For a European company like Runehold, a few things are non-negotiable:

  1. Personal data should be encrypted at rest. TDE (Transparent Data Encryption), Always Encrypted (for specific columns), or filesystem encryption. Standard practice now.
  2. Encrypted connections. Force TLS between applications and the database. Encrypt=True in connection strings.
  3. Access logging. Every read of PII by a human user should be logged. SQL Server Audit is the tool; external SIEM (Splunk, Datadog) reads and archives.
  4. Right to erasure — see lesson 31 on backups. Live data deleted immediately; backups roll off in a defined window.
  5. Data portability — need to be able to export a customer’s data on demand. Build the export query now; don’t try to figure it out when legal calls.

None of these change the core security model, but they add constraints. For Runehold specifically, the DPO (Data Protection Officer) dictates which columns count as “personal data” and what encryption level each gets. Consult them; don’t invent policies on your own.

Run this on your own machine

USE Runehold;
GO

-- 1. Create a read-only role, add permissions
CREATE ROLE RolesDemo_ReadOnly;
GRANT SELECT ON SCHEMA::Sales TO RolesDemo_ReadOnly;

-- 2. Create a user (no login, for demo only)
CREATE USER RolesDemo_User WITHOUT LOGIN;
ALTER ROLE RolesDemo_ReadOnly ADD MEMBER RolesDemo_User;

-- 3. Test what the user can do
EXECUTE AS USER = 'RolesDemo_User';
SELECT TOP 1 * FROM Sales.Customer;  -- works
SELECT TOP 1 * FROM Sales.Customer;
-- INSERT INTO Sales.Customer (Name, Country) VALUES ('X', 'NL');  -- denied
REVERT;

-- 4. Audit: what permissions does this user have?
SELECT p.class_desc,
       OBJECT_SCHEMA_NAME(p.major_id) + '.' + OBJECT_NAME(p.major_id) AS obj,
       p.permission_name, p.state_desc
FROM sys.database_permissions AS p
JOIN sys.database_principals AS pr ON pr.principal_id = p.grantee_principal_id
WHERE pr.name IN ('RolesDemo_User', 'RolesDemo_ReadOnly');

-- 5. Cleanup
ALTER ROLE RolesDemo_ReadOnly DROP MEMBER RolesDemo_User;
DROP USER RolesDemo_User;
DROP ROLE RolesDemo_ReadOnly;

Next lesson: tempdb — the shared playground every session uses, and the source of more mysterious performance problems than any other single resource.

Search