The Following Scripts can be used to create a SQL Server Database and SQL Server user with Minimum permissions.
Executive summary
This design follows least privilege, clear ownership boundaries, and containment.
The application account can do exactly what it needs to do — no more, no less — and any failure, bug, or compromise is confined to one schema in one database.
If something goes wrong, the blast radius is intentionally small.
-
Database ownership is explicit and controlled
What we do
-
The database is owned by a sysadmin login (sa or equivalent).
-
Application accounts are never database owners.
Why this matters:
-
Database ownership implies full control: security, schema, objects, and even dropping the database.
-
Making an application user the owner creates implicit privilege escalation paths and bypasses permission checks.
-
By keeping ownership with a sysadmin, all authority remains with administrators and not with the application.
Risks avoided
-
Accidental or malicious schema/security changes
-
Hidden privilege inheritance through ownership chaining
-
Irreversible permission drift
-
Application identity is isolated and non-privileged
What we do
-
The application uses a dedicated SQL login (cdata_user).
-
The login:
-
Is not a member of any server role
-
Has no permissions outside the target database
-
Has a default database explicitly set
Why this matters:
-
A dedicated login provides clear attribution in audit logs.
-
The login cannot see or access other databases.
-
Compromise of the login does not expose the server or other applications.
Risks avoided
-
Lateral movement across databases
-
Shared-credential ambiguity
-
Accidental access to system databases
-
A dedicated schema creates a hard security boundary
What we do
-
All application objects live in a dedicated schema (SYNC).
-
The schema is owned by dbo, not by the application user.
Why this matters:
-
Schemas are first-class permission boundaries in SQL Server.
-
Permissions can be granted at the schema level instead of per-object.
-
Ownership of the schema remains with administrators, preventing privilege abuse.
Risks avoided
-
Access to dbo or system schemas
-
Ownership chaining across unrelated objects
-
Uncontrolled access to future tables
4. Permissions are granted via a role, not directly to the user
What we do
-
A database role (SYNC_rw) holds all permissions.
-
The application user is only a member of that role.
Why this matters:
-
Role-based permissions are easier to audit, reason about, and revoke.
-
Users can be rotated without touching permissions.
-
Permissions are explicit, visible, and centralized.
Risks avoided
-
Permission sprawl
-
Difficult audits
-
Orphaned or forgotten grants
5. Only minimum required permissions are granted
What the application can do
Within the SYNC schema only:
-
Read data (SELECT)
-
Write data (INSERT, UPDATE, DELETE)
-
Create tables
-
Drop or alter its own tables
What it cannot do
-
Modify security or roles
-
Access other schemas or databases
-
Create procedures, views, or users
-
Change database settings
-
Escalate privileges
Why this matters:
-
The application cannot “grow” its own authority.
-
Bugs, SQL injections, or misuse are contained.
-
New tables outside SYNC are not automatically exposed.
Risks avoided
-
Database-wide compromise
-
Accidental schema damage
-
Silent privilege escalation
6. Default database and schema prevent unsafe behavior
What we do
-
Login default database is set to the application database.
-
User default schema is set to SYNC.
Why this matters:
-
Prevents connections landing in master.
-
Prevents accidental creation of objects in the ‘dbo’ schema.
-
Reduces reliance on developers by always qualifying object names correctly.
Risks avoided
-
System database modification
-
Objects created in the wrong schema
-
Environment-dependent behavior
7. No reliance on “implicit trust” features
What we explicitly do NOT enable
-
db_owner
-
TRUSTWORTHY
-
Cross-database chaining
-
Ownership transfer to application users
Why this matters:
-
These features bypass permission checks.
-
They are common causes of audit findings.
-
They increase blast radius dramatically.
8. Failure modes are predictable and safe
If the application misbehaves
-
Errors surface immediately as permission errors.
-
Damage is limited to the SYNC schema.
-
The database and server remain intact.
If credentials are compromised
-
Attacker is confined to one schema.
-
Cannot read system data or other applications.
-
Cannot persist access via security changes.
9. This model aligns with security and compliance standards
This approach aligns with:
-
Principle of Least Privilege (NIST, CIS)
-
Separation of Duties
-
SOC 2 / ISO 27001 audit expectations
-
Microsoft SQL Server security best practices
Auditors typically flag:
-
Application users with db_owner
-
Shared credentials
-
Implicit ownership
-
Cross-schema access
This design avoids all of those.
Bottom line
This design ensures the application can function normally while minimizing risk, limiting blast radius, and preserving administrative control.
It is:
-
Safer than granting db_owner
-
Easier to audit than object-level grants
-
More secure than shared or over-privileged accounts
-
Operationally predictable under failure
If something breaks, it breaks small.
SCRIPTS
-
Master setup script (paste and run as sa / sysadmin)
/* ============================
MASTER SETUP SCRIPT
Creates:
- Database: sync_source_DB
- SQL login: cdata_user
- DB user: cdata_user
- Schema: SYNC (owned by dbo)
- Role: SYNC_rw
Grants minimum permissions:
- SELECT/INSERT/UPDATE/DELETE on schema SYNC
- CREATE TABLE in database
- ALTER on schema SYNC (enables DROP/ALTER objects in SYNC)
Sets:
- Default schema = SYNC
- Default database = sync_source_DB
Optional:
- DENY VIEW ANY DATABASE (hides other DBs)
============================ */
USE master;
GO
DECLARE
@DbName sysname = N'sync_source_DB',
@Login sysname = N'cdata_user',
@User sysname = N'cdata_user',
@Schema sysname = N'SYNC',
@Role sysname = N'SYNC_rw',
@Pwd nvarchar(256) = N'Put-A-Strong-Password-Here'; -- CHANGE ME
/* 1) Create database (if needed) */
IF DB_ID(@DbName) IS NULL
BEGIN
DECLARE @sql nvarchar(max) = N'CREATE DATABASE ' + QUOTENAME(@DbName) + N';';
EXEC (@sql);
END
GO
/* 2) Ensure DB is owned by sa (avoid odd ownership/permission issues) */
ALTER AUTHORIZATION ON DATABASE::[sync_source_DB] TO [sa];
GO
/* 3) Create login (if needed) */
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'cdata_user')
BEGIN
DECLARE @sql nvarchar(max) =
N'CREATE LOGIN ' + QUOTENAME(N'cdata_user') +
N' WITH PASSWORD = ' + QUOTENAME(N'Put-A-Strong-Password-Here', '''') +
N', CHECK_POLICY = ON, CHECK_EXPIRATION = ON;';
EXEC (@sql);
END
GO
/* 4) Set login default DB (so connections don’t land in master) */
ALTER LOGIN [cdata_user] WITH DEFAULT_DATABASE = [sync_source_DB];
GO
/* Optional: hide other databases from this login (cosmetic) */
-- DENY VIEW ANY DATABASE TO [cdata_user];
-- GO
/* 5) Create user in the target DB and repair mapping if needed */
USE [sync_source_DB];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'cdata_user')
CREATE USER [cdata_user] FOR LOGIN [cdata_user];
GO
-- Repairs orphaned mapping if it ever happens; safe to run
ALTER USER [cdata_user] WITH LOGIN = [cdata_user];
GO
GRANT CONNECT TO [cdata_user];
GO
/* 6) Create schema SYNC owned by dbo */
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'SYNC')
EXEC('CREATE SCHEMA [SYNC] AUTHORIZATION dbo;');
GO
/* 7) Default schema so CREATE TABLE goes into SYNC.* */
ALTER USER [cdata_user] WITH DEFAULT_SCHEMA = [SYNC];
GO
/* 8) Create role to hold permissions */
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'SYNC_rw' AND type = 'R')
CREATE ROLE [SYNC_rw];
GO
/* 9) Grant minimum permissions */
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[SYNC] TO [SYNC_rw];
GRANT ALTER ON SCHEMA::[SYNC] TO [SYNC_rw]; -- enables DROP/ALTER within SYNC
GRANT CREATE TABLE TO [SYNC_rw]; -- enables CREATE TABLE in this DB
GO
/* 10) Add user to role */
ALTER ROLE [SYNC_rw] ADD MEMBER [cdata_user];
GO
/* 11) Optional “good defaults” for many app DBs */
-- ALTER DATABASE [sync_source_DB] SET AUTO_CLOSE OFF;
-- ALTER DATABASE [sync_source_DB] SET AUTO_SHRINK OFF;
-- ALTER DATABASE [sync_source_DB] SET READ_COMMITTED_SNAPSHOT ON;
-- ALTER DATABASE [sync_source_DB] SET RECOVERY SIMPLE;
-- GO
/* 12) Quick verification output */
SELECT
DB_NAME() AS db,
SUSER_SNAME() AS server_login,
USER_NAME() AS db_user,
SCHEMA_NAME() AS default_schema;
GO
