33. Security & User Management -- Controlling Access

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• SQL Server authentication (SQL, Windows)
• CREATE LOGIN and CREATE USER
• Database roles and permissions
• GRANT, REVOKE, DENY
• Column-level security
• Row-level security (RLS)
• Encryption (TDE, column encryption)
• Common security mistakes
• Best practices

Unrestricted access = data breach. Authentication and authorization protect data. This article teaches access control.

Why Security Matters for Backend Developers

Data protection required:

Students see everyone's grades (bad)
Teachers see student salaries (illegal)
Public sees fee amounts (data breach)
    v
Logins + roles + permissions = controlled access
    v
Each user sees only authorized data

Security prevents leaks.

Authentication: Login Creation

CREATE LOGIN (server-level):

-- SQL authentication (password)
CREATE LOGIN BackendAppUser WITH PASSWORD = 'SecureP@ssw0rd!';

-- Windows authentication
CREATE LOGIN [DOMAIN\BackendService] FROM WINDOWS;

-- Change password
ALTER LOGIN BackendAppUser WITH PASSWORD = 'NewSecureP@ss!';

-- Disable login
ALTER LOGIN BackendAppUser DISABLE;

LOGIN: Server access. One per database user.

Authorization: User & Role

CREATE USER (database-level):

-- Link login to user
CREATE USER BackendAppUser FOR LOGIN BackendAppUser;

-- Add to role
ALTER ROLE db_datareader ADD MEMBER BackendAppUser;
ALTER ROLE db_datawriter ADD MEMBER BackendAppUser;

USER: Database access (mapped to LOGIN). ROLES: Collections of permissions.

Example 1: API User with Limited Access

Create read-only user for reporting:

-- Server: Create login
CREATE LOGIN ReportUser WITH PASSWORD = 'Report@123!';

-- Database: Create user
CREATE USER ReportUser FOR LOGIN ReportUser;

-- Add to reader role (SELECT only)
ALTER ROLE db_datareader ADD MEMBER ReportUser;

-- Revoke write permissions
DENY INSERT, UPDATE, DELETE ON DATABASE::SchoolDB TO ReportUser;

-- Result: ReportUser can SELECT, not INSERT/UPDATE/DELETE

Minimal permissions. Read-only for reports.

Example 2: Teacher User (Role-based Access)

Create teacher user with specific permissions:

-- Create teacher role
CREATE ROLE TeacherRole;

-- Grant permissions
GRANT SELECT ON dbo.Student TO TeacherRole;
GRANT SELECT ON dbo.Exam TO TeacherRole;
GRANT SELECT ON dbo.ExamResult TO TeacherRole;
GRANT UPDATE ON dbo.ExamResult TO TeacherRole;  -- Can update marks
GRANT INSERT ON dbo.ExamResult TO TeacherRole;  -- Can insert results

-- Deny sensitive tables
DENY SELECT ON dbo.FeeAccount TO TeacherRole;   -- Can't see fees
DENY SELECT ON dbo.User TO TeacherRole;         -- Can't see passwords

-- Create teacher user
CREATE USER MrsRao FOR LOGIN MrsRao;
ALTER ROLE TeacherRole ADD MEMBER MrsRao;

Teachers: See students/exams/results, not fees/passwords.

Example 3: Row-Level Security (RLS)

Students see only own results:

-- Create security policy
CREATE FUNCTION dbo.rls_StudentFilter(@StudentId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
    SELECT 1 AS result
    WHERE @StudentId = CAST(SESSION_CONTEXT(N'StudentId') AS INT)
    OR CAST(SESSION_CONTEXT(N'Role') AS NVARCHAR(MAX)) = 'Admin';

-- Create policy
CREATE SECURITY POLICY rls_ExamResultFilter
    ADD FILTER PREDICATE dbo.rls_StudentFilter(StudentId) 
    ON dbo.ExamResult;

-- Usage:
EXEC sp_set_session_context @key = 'StudentId', @value = 5;
EXEC sp_set_session_context @key = 'Role', @value = 'Student';

SELECT * FROM ExamResult;  -- Only StudentId = 5's results

RLS: Student sees only own data (automatic filtering).

Example 4: Column-Level Security

Hide sensitive columns:

-- Create view hiding sensitive columns
CREATE VIEW StudentPublic AS
SELECT 
    StudentId,
    Name,
    ClassName,
    RollNumber
    -- OMIT: DateOfBirth, ParentPhone, Address (sensitive)
FROM Student;

-- Grant access to view, not table
GRANT SELECT ON dbo.StudentPublic TO [public];
DENY SELECT ON dbo.Student TO [public];

-- Users query view (safe columns):
SELECT * FROM StudentPublic;  -- No sensitive data

View restricts access to sensitive columns.

Example 5: Transparent Data Encryption (TDE)

Encrypt entire database at rest:

-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey@123!';

-- Create certificate
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

-- Create database encryption key
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert;

-- Enable encryption
ALTER DATABASE SchoolDB SET ENCRYPTION ON;

-- Check status:
SELECT * FROM sys.dm_database_encryption_keys;
-- Result: encryption_state = 3 (encrypted)

TDE: Entire database encrypted on disk. Transparent to queries.

Example 6: Column Encryption

Encrypt sensitive columns:

-- Create key
CREATE COLUMN MASTER KEY CMK_PasswordKey
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/ThumbprintHere'
);

-- Create encryption key
CREATE COLUMN ENCRYPTION KEY CEK_PasswordKey
WITH VALUES (
    COLUMN_MASTER_KEY = CMK_PasswordKey,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x...
);

-- Encrypt column
ALTER TABLE User
ALTER COLUMN PasswordHash 
    ENCRYPTED WITH (
        ENCRYPTION_TYPE = DETERMINISTIC,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = CEK_PasswordKey
    );

-- Result: PasswordHash encrypted in database

Column encryption: Encrypt sensitive columns.

Example 7: Audit Logging

Track who accessed what:

-- Create audit
CREATE SERVER AUDIT AuditSchoolDB
TO FILE (FILEPATH = 'C:\Audits\');

-- Create database audit
CREATE DATABASE AUDIT SPECIFICATION AuditStudentAccess
FOR SERVER AUDIT AuditSchoolDB
ADD (SELECT ON dbo.Student BY PUBLIC) WITH (QUEUE_DELAY = 1000);

-- Enable
ALTER SERVER AUDIT AuditSchoolDB WITH (STATE = ON);
ALTER DATABASE AUDIT SPECIFICATION AuditStudentAccess WITH (STATE = ON);

-- Review audit log
SELECT * FROM fn_get_audit_file('C:\Audits\*', DEFAULT, DEFAULT);

Audit: Track who queried what and when.

---

Common Security Mistakes

Mistake 1: sa account used for applications

Wrong:

-- Application uses sa login
Connection string: Server=localhost; User=sa; Password=admin;
-- sa = full admin access, no audit trail

Result: No accountability. Any user can drop database.

Fix: Dedicated app user:

CREATE LOGIN AppUser WITH PASSWORD = '...';
ALTER ROLE db_datareader ADD MEMBER AppUser;
ALTER ROLE db_datawriter ADD MEMBER AppUser;
-- Only what app needs

Mistake 2: Weak passwords

Wrong:

CREATE LOGIN User1 WITH PASSWORD = '123456';

Result: Easy to crack.

Fix: Strong passwords:

CREATE LOGIN User1 WITH PASSWORD = 'SecureP@ss2024!';
-- Min 12 chars, uppercase, lowercase, numbers, symbols

Mistake 3: Hardcoded credentials

Wrong:

// Hardcoded in code
string conn = "Server=localhost; User=admin; Password=password;";

Result: Anyone reading source sees credentials.

Fix: Config file or vault:

// Read from secure location
string password = GetPasswordFromVault("DatabaseUser");

Mistake 4: No role-based access

Wrong:

-- All users get same permissions
ALTER ROLE db_datareader ADD MEMBER Everyone;
ALTER ROLE db_datawriter ADD MEMBER Everyone;
-- Teachers see student salaries, students see fees

Fix: Role-based:

CREATE ROLE TeacherRole;
GRANT SELECT ON Student TO TeacherRole;
DENY SELECT ON FeeAccount TO TeacherRole;

Mistake 5: Unencrypted sensitive data

Wrong:

-- Passwords stored in plain text
INSERT INTO User (PasswordHash) VALUES ('mypassword');

Result: Database breach = exposed passwords.

Fix: Encrypt or hash:

-- Use HASHBYTES or column encryption
INSERT INTO User (PasswordHash) VALUES (HASHBYTES('SHA2_256', 'mypassword'));

---

Best Practices for Security

1. Use Windows auth when possible -- No passwords in apps
2. Principle of least privilege -- Only needed permissions
3. Strong passwords -- 12+ chars, mixed case, symbols
4. Separate roles -- Admin, Teacher, Student
5. Encrypt sensitive data -- TDE + column encryption
6. Enable auditing -- Track access
7. Regular backups -- Encrypted, off-site
8. Update SQL Server -- Security patches

---

🎯 Q1: What's the difference between LOGIN and USER?

LOGIN: Server-level authentication.

CREATE LOGIN AppUser WITH PASSWORD = '...';

USER: Database-level, maps to LOGIN.

CREATE USER AppUser FOR LOGIN AppUser;

LOGIN: How you authenticate. USER: Which database.

🎯 Q2: What are database roles?

Collections of permissions:

db_datareader   -- SELECT only
db_datawriter   -- INSERT/UPDATE/DELETE
db_owner        -- Full access
db_ddladmin     -- ALTER/CREATE

Add user to role: ALTER ROLE db_datareader ADD MEMBER User;

🎯 Q3: What's DENY vs REVOKE?

REVOKE: Remove permission (back to default).

REVOKE SELECT ON Student FROM TeacherRole;

DENY: Explicitly block (even if role has it).

DENY SELECT ON FeeAccount FROM TeacherRole;
-- Teacher can't see, even if db_datareader role allows

DENY overrides.

🎯 Q4: What's row-level security (RLS)?

Automatic filtering by user:

Student 1: SELECT * FROM ExamResult  -- Sees only own results
Student 2: SELECT * FROM ExamResult  -- Sees only own results

RLS predicate enforces transparently.

Use for: Multi-tenant, data isolation.

🎯 Q5: Should I encrypt all columns?

No, only sensitive:

• Passwords (hash actually)
• Credit card numbers
• Personal data (SSN, phone)

Don't encrypt: IDs, status, dates (need to index/filter).

🎯 Q6: How do I enable auditing?

CREATE SERVER AUDIT MyAudit TO FILE (FILEPATH = 'C:\...');
CREATE DATABASE AUDIT SPECIFICATION MySpec
FOR SERVER AUDIT MyAudit
ADD (SELECT ON dbo.Student);
ALTER SERVER AUDIT MyAudit WITH (STATE = ON);

Tracks SELECT on Student. Review audit log periodically.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Security and access control in SQL Server. Try these prompts:

• "Show how to create API user with SELECT/INSERT permissions only"
• "Explain role-based access: teacher sees students, not fees"
• "What's row-level security? Show student sees only own results"
• "How do I encrypt sensitive columns (passwords, SSN)?"
• "Quiz me: ask 5 questions about logins, roles, permissions, encryption"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

34. Data Migration & Integration -- Moving Data Safely ->