SQL Server Security
SQL Server 2000 implements security at three levels: server logins, database users, and object permissions. This guide covers authentication, authorization, and hardening.
Authentication Modes
| Mode | Description | When to Use |
|---|
| Windows Authentication | Uses AD/Windows accounts. No separate password. | Recommended for intranet apps |
| Mixed Mode | Allows both Windows and SQL Server logins. | When non-Windows clients need access |
Creating Logins and Users
-- Create a Windows login
EXEC sp_grantlogin 'CORP\jsmith'
-- Create a SQL Server login
EXEC sp_addlogin 'appuser', 'Str0ngP@ss!', 'FlamenetDB'
-- Map login to a database user
USE FlamenetDB
EXEC sp_grantdbaccess 'CORP\jsmith', 'jsmith'
-- Add user to a database role
EXEC sp_addrolemember 'db_datareader', 'jsmith'
EXEC sp_addrolemember 'db_datawriter', 'jsmith'
Fixed Database Roles
| Role | Permissions |
|---|
| db_owner | Full control of the database |
| db_datareader | SELECT on all tables and views |
| db_datawriter | INSERT, UPDATE, DELETE on all tables |
| db_ddladmin | CREATE, ALTER, DROP objects |
| db_securityadmin | Manage roles and permissions |
| db_denydatareader | Cannot SELECT any data |
| db_denydatawriter | Cannot modify any data |
Object-Level Permissions
-- Grant SELECT on a specific table
GRANT SELECT ON Employees TO jsmith
-- Grant EXECUTE on a stored procedure
GRANT EXECUTE ON usp_GetEmployeesByDept TO webapp_role
-- Revoke permissions
REVOKE INSERT ON Employees FROM jsmith
-- Deny (overrides any grant)
DENY DELETE ON Employees TO jsmith
Hardening Checklist
- Use Windows Authentication mode when possible
- Set a strong
sa password (even if not using Mixed Mode)
- Remove the BUILTIN\Administrators login from sysadmin role
- Disable
xp_cmdshell unless required
- Drop sample databases (
pubs, Northwind) on production servers
- Apply SQL Server 2000 SP4 and all post-SP4 security patches
- Restrict SQL Server port (1433) access with firewalls
- Hide the SQL Server instance to prevent enumeration