SQL Server Security — Logins, Users, Roles and Row-Level Security

SQL Server’s security model operates at two levels: the server level (logins — who can connect to the SQL Server instance) and the database level (users — what an authenticated login can do within a specific database). The principle of least privilege — granting only the minimum permissions necessary — is the foundation of secure database access. The BlogApp API should connect with a login that has only db_datareader, db_datawriter, and db_ddladmin (for migrations) — never sa or db_owner.

Logins, Users and Permissions #

-- ── Server-level: create a login ──────────────────────────────────────────
USE master;
GO

-- SQL Server Authentication login (use Windows Auth in on-premises where possible)
CREATE LOGIN blogapp_api WITH PASSWORD = N'Str0ng!Pass#BlogApp2025',
    CHECK_EXPIRATION = ON,    -- enforce password expiry policy
    CHECK_POLICY     = ON;    -- enforce Windows password complexity policy
GO

-- ── Database-level: create a user for the login ───────────────────────────
USE BlogApp;
GO
CREATE USER blogapp_api FOR LOGIN blogapp_api;
GO

-- Grant minimum required permissions:
ALTER ROLE db_datareader ADD MEMBER blogapp_api;   -- SELECT on all tables
ALTER ROLE db_datawriter ADD MEMBER blogapp_api;   -- INSERT/UPDATE/DELETE
ALTER ROLE db_ddladmin   ADD MEMBER blogapp_api;   -- CREATE/ALTER/DROP (for migrations)
-- Remove db_ddladmin in production if migrations run via a separate deployment account

-- Grant EXECUTE on specific stored procedures:
GRANT EXECUTE ON dbo.usp_PublishPost   TO blogapp_api;
GRANT EXECUTE ON dbo.usp_GetPublishedPosts TO blogapp_api;
GO

-- ── Row-Level Security — multi-tenant filtering at database level ──────────
-- Scenario: multiple blog sites share one database; each site sees only its data

-- 1. Add TenantId to Posts (if multi-tenant):
-- ALTER TABLE dbo.Posts ADD TenantId INT NOT NULL DEFAULT 1;

-- 2. Create the predicate function
CREATE OR ALTER FUNCTION dbo.fn_PostTenantFilter(@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
    SELECT 1 AS fn_result
    WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);
        -- SESSION_CONTEXT is set by the app on each connection
GO

-- 3. Create the security policy
CREATE SECURITY POLICY PostTenantPolicy
    ADD FILTER PREDICATE dbo.fn_PostTenantFilter(TenantId) ON dbo.Posts
    WITH (STATE = ON);
GO

-- 4. Application sets context on each request:
-- EXEC sp_set_session_context N'TenantId', @TenantId;
-- Now all queries on Posts automatically filter to the tenant's rows

-- ── Dynamic Data Masking — hide sensitive data from low-privilege accounts ─
ALTER TABLE dbo.Users
    ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
-- Low-privilege users see: aXXX@XXXX.com (first char + masked domain)
-- db_owner and users with UNMASK permission see the real value

-- ── Always Encrypted — client-side column encryption ─────────────────────
-- Sensitive columns encrypted before leaving the client — SQL Server never sees plaintext
-- Requires Always Encrypted driver configuration in the connection string
-- Best for: SSN, credit card numbers, health data

Permission Reference #

Common Mistakes #

Mistake 1 — Application connecting as sa or db_owner (excessive privileges) #

❌ Wrong — connection string with User Id=sa; SQL injection gives attacker unrestricted server access.

✅ Correct — dedicated application login with db_datareader + db_datawriter only; limit blast radius.

Mistake 2 — Storing SQL Server password in connection string in source control #

❌ Wrong — password in appsettings.json committed to Git; credentials exposed to all developers.

✅ Correct — use environment variables, Azure Key Vault, or Managed Identity; never commit credentials.