SQL Server Security: Logins, Users, Roles & Permissions

The Critical Distinction: Logins vs. Users

-- LOGIN: Server-level (allows connection to SQL Server)
CREATE LOGIN john_login WITH PASSWORD = 'SecurePassword123!';
-- John can now CONNECT to SQL Server

-- USER: Database-level (allows access to specific database)
USE MyDatabase;
CREATE USER john_user FOR LOGIN john_login;
-- John can now access MyDatabase

-- Hierarchy:
Server → [Logins] → Connect to SQL Server
         ↓
Database → [Users] → Access specific database
           ↓
[Roles] → Get permissions
           ↓
[Permissions] → Execute queries, read tables, etc.

-- Without Login: Cannot connect
// Without User: Cannot access database (even if login exists!)

Authentication: Windows vs. SQL Server

Windows Authentication (Recommended)

-- Windows AD account mapped to SQL Server login
CREATE LOGIN [CONTOSO\john] FROM WINDOWS;

-- Advantages:
// - Credentials delegated to Windows/Kerberos (not SQL Server)
// - No password in connection string
// - Single sign-on (domain login = SQL access)
// - Auditing tied to Windows events

-- Usage:
Connection string: Server=sql1;Database=MyDB;Integrated Security=true;
// Uses current Windows user credentials

SQL Server Authentication

-- SQL Server maintains password (not recommended)
CREATE LOGIN sa_account WITH PASSWORD = 'ComplexPassword123!';

-- Disadvantages:
// - Password must be managed by SQL Server (vulnerability)
// - Credentials in connection string (risky)
// - No Windows audit trail
// - Weaker than Windows auth

-- Usage (only if necessary):
Connection string: Server=sql1;Database=MyDB;User ID=sa_account;Password=...;
// Password in config file = security risk!

Server-Level Roles

Fixed server roles grant permissions at the server level (affects entire SQL Server instance).

-- Grant server role
ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\john];

-- Check who is sysadmin
SELECT name, type_desc
FROM sys.server_principals
WHERE IS_MEMBER('sysadmin') = 1;

-- ⚠️ WARNING: sysadmin has COMPLETE control!
// Can read all data, delete all data, modify server config, etc.
// Only DBAs should be sysadmin

Database-Level Roles

Database roles grant permissions within a single database (safer than server roles).

-- Create application user (least privilege)
USE MyDatabase;
CREATE USER app_user FOR LOGIN app_login;

-- Grant minimal permissions
ALTER ROLE db_datareader ADD MEMBER app_user;
-- Can only SELECT (read-only)

-- Grant specific table permissions
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO app_user;
-- More granular control

-- Deny dangerous operations
DENY DROP TABLE TO app_user;
DENY ALTER DATABASE TO app_user;
// Explicit deny (strongest protection)

GRANT, DENY, REVOKE: Permission Hierarchy

Permission Precedence

-- Hierarchy (strongest to weakest):
1. DENY (overrides everything)
2. GRANT (if not denied)
3. No permission (implicitly denied)

-- Example:
Role says: GRANT SELECT
User says: DENY SELECT
Result: DENIED (DENY wins!)

-- Another example:
Server role: GRANT on table
Database role: DENY on same table
Result: DENIED (explicit DENY strongest)

GRANT: Give Permission

-- Grant SELECT on all tables
GRANT SELECT ON SCHEMA::dbo TO app_user;
-- app_user can SELECT all dbo tables

-- Grant EXECUTE on stored procedure
GRANT EXECUTE ON dbo.uspInsertOrder TO app_user;
-- app_user can run stored procedure (but maybe not access underlying tables!)

-- Grant INSERT on specific table
GRANT INSERT ON dbo.Orders TO app_user;
// Only INSERT (not SELECT, DELETE, UPDATE)

DENY: Explicitly Block Permission

-- Deny dangerous operations
DENY ALTER DATABASE TO app_user;
DENY DROP TABLE TO app_user;
DENY ALTER ROLE TO app_user;
// Even if role grants these, DENY blocks them

-- Override inherited permissions
Role: db_datawriter (INSERT, UPDATE, DELETE)
DENY INSERT ON dbo.FinancialData TO app_user;
// app_user can UPDATE/DELETE but NOT INSERT sensitive table

REVOKE: Remove Permission

-- Remove permission (revert to no access)
REVOKE SELECT ON dbo.Orders FROM app_user;
// app_user no longer has SELECT (back to default: denied)

-- Difference between DENY and REVOKE:
DENY:   Actively blocked (can't get through GRANT elsewhere)
REVOKE: Permission removed (can still inherit through role)

Principle of Least Privilege (PoLP)

Grant only the minimum permissions needed for a user to perform their job.

-- ✗ BAD (excessive permissions):
CREATE LOGIN app_login WITH PASSWORD = 'pwd';
USE MyDatabase;
CREATE USER app_user FOR LOGIN app_login;
ALTER ROLE db_owner ADD MEMBER app_user;
// App user has COMPLETE database control!
// Risk: Accidental or malicious data deletion

-- ✓ GOOD (minimal permissions):
CREATE LOGIN app_login WITH PASSWORD = 'pwd';
USE MyDatabase;
CREATE USER app_user FOR LOGIN app_login;

-- Grant only what's needed
ALTER ROLE db_datareader ADD MEMBER app_user;  -- Can read
GRANT EXECUTE ON dbo.uspInsertOrder TO app_user;  -- Can execute proc
GRANT UPDATE ON dbo.Orders TO app_user;  -- Can update orders

-- Deny sensitive operations
DENY DELETE ON dbo.Orders FROM app_user;  -- Cannot delete
DENY ALTER TABLE FROM app_user;  -- Cannot modify schema

// Result: App can do its job, nothing more

    
Security Audit Queries

    
-- Find all sysadmins (most dangerous role)
SELECT name, type_desc
FROM sys.server_principals
WHERE IS_MEMBER('sysadmin') = 1;

-- Find all logins
SELECT name, type_desc, create_date, modify_date
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G');

-- Find database users
USE MyDatabase;
SELECT name, type_desc, authentication_type
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G');

-- Check if SQL authentication logins exist (risky!)
SELECT name, type_desc
FROM sys.server_principals
WHERE type = 'S';  -- S = SQL authenticated


    
Common Mistakes

    
Mistake 1: Granting sysadmin to Application Users

    
-- ✗ DANGEROUS:
ALTER SERVER ROLE sysadmin ADD MEMBER app_user;
// App can now: Drop databases, read sensitive data, modify server

-- ✓ CORRECT:
ALTER ROLE db_datareader ADD MEMBER app_user;
// App can only read (limited scope)


    
Mistake 2: SQL Authentication Instead of Windows

    
-- ✗ RISKY:
CREATE LOGIN app_login WITH PASSWORD = 'hardcodedpwd';
// Password in connection string in config file!
// Password in logs, memory, connection strings
// No Windows audit trail

-- ✓ SECURE:
CREATE LOGIN [CONTOSO\appservice] FROM WINDOWS;
// Service account in Windows, no password in SQL
// Kerberos delegation
// Windows audit trail


    
Mistake 3: Confusing Logins and Users

    
-- ✗ INCOMPLETE:
CREATE LOGIN john_login WITH PASSWORD = 'pwd';
-- John can connect to SQL Server, but...

SELECT * FROM dbo.Orders;
-- ERROR: User does not have permission!
// Why? No DATABASE USER created!

-- ✓ COMPLETE:
CREATE LOGIN john_login WITH PASSWORD = 'pwd';
USE MyDatabase;
CREATE USER john_user FOR LOGIN john_login;
GRANT SELECT ON dbo.Orders TO john_user;
-- NOW John can access orders


    
Best Practices

    

      
☐ Use Windows Authentication (Integrated Security) for internal applications.
      
☐ Apply Principle of Least Privilege: Grant minimal permissions needed.
      
☐ Never grant sysadmin to application accounts (only DBAs).
      
☐ Use fixed database roles (db_datareader, db_datawriter) for standard access.
      
☐ Create custom database roles for complex permission requirements.
      
☐ Use DENY to prevent dangerous operations explicitly.
      
☐ Regularly audit permissions (quarterly reviews).
      
☐ Remove orphaned users (logins deleted, users remain).
      
☐ Disable or delete unused logins.
      
☐ Enforce strong password policies (if using SQL authentication).
      
☐ Use encryption for sensitive data (Transparent Data Encryption, Always Encrypted).
      
☐ Enable SQL Server auditing to track permission changes and access.
    

    
The Bottom Line

    
SQL Server security starts with understanding the architecture: Logins authenticate to SQL Server, Users grant access to databases, Roles bundle permissions, and Permissions control what users can do.

    
The golden rule: Principle of Least Privilege. Grant only what's needed. Block dangerous operations with DENY. Use Windows Authentication. Audit regularly.

    
Most security breaches happen not because of hacking, but because of misconfigured permissions—a user with excessive access, a shared admin account, a hardcoded password in a config file. Get the basics right: logins, users, roles, and permissions. Build from there.