mssql

This tool automates the creation of SQL Server database users with secure password management through Google Cloud Secret Manager. It uses a state machine pattern to ensure reliable, atomic operations for user provisioning and permission assignment.

Features

• ✅ Automated SQL Server user creation
• 🗑️ Safe user deletion with secret cleanup
• 🔄 Password rotation for existing users
• 🔐 Secure password generation and storage in GCP Secret Manager
• 🔑 Granular permission management (Read, ReadWrite, ReadWriteExecute)
• 🔄 State machine-based execution for reliability

Build

./zig/zig build mssql

Usage

Basic Command Structure

Create User

./mssql user create [username] \
  --server [SQL_SERVER] \
  --database [DATABASE_NAME] \
  --environment [ENVIRONMENT] \
  --permissions [PERMISSION_LEVEL]

Delete User

./mssql user delete [username] \
  --server [SQL_SERVER] \
  --database [DATABASE_NAME] \
  --environment [ENVIRONMENT]

Rotate Password

./mssql user rotate-password [username] \
  --server [SQL_SERVER] \
  --database [DATABASE_NAME] \
  --environment [ENVIRONMENT]

Parameters

Note: Either --database or --all-databases must be specified for the create command, but not both.

Permission Levels

With --table, the user gets no roles, only a grant on that table:

rwx isn’t allowed with --table. If the user already exists and belongs to any database role (for example db_datareader from an earlier run without --table), the command stops without granting, because the role would still give database-wide access. Before removing a role, check why the user has it and what depends on that access; remove it only if that’s safe, or use a new user instead. ALTER is what SET IDENTITY_INSERT requires, but it also lets the user TRUNCATE the table or change its columns, so only use it when inserts must set the identity column.

Examples

Create User Examples

# Create read-only user
./mssql user create app_readonly_user \
  --server myserver.database.windows.net \
  --database myapp_db \
  --environment staging

# Create read-write user with verbose logging
./mssql user create app_service_user \
  --server myserver.database.windows.net \
  --database myapp_db \
  --environment production \
  --permissions rw \
  --verbose

# Create user with full permissions
./mssql user create admin_user \
  --server myserver.database.windows.net \
  --database myapp_db \
  --environment dev \
  --permissions rwx

# Create a user that can only read and write one table, and insert with an explicit ID
./mssql user create launchbot_client_creator \
  --server ha-dev-azsqldb.database.windows.net \
  --database hAUAT_Clean \
  --environment staging \
  --permissions rw \
  --table dbo.Clients \
  --identity-insert

# Create user on ALL databases at once
./mssql user create app_service_user \
  --server myserver.database.windows.net \
  --all-databases \
  --environment production \
  --permissions rw

Delete User Examples

# Delete user and associated secrets
./mssql user delete app_readonly_user \
  --server myserver.database.windows.net \
  --database myapp_db \
  --environment staging

# Delete user with verbose logging
./mssql user delete old_service_user \
  --server myserver.database.windows.net \
  --database myapp_db \
  --environment production \
  --verbose

Rotate Password Examples

# Rotate password for existing user
./mssql user rotate-password app_service_user \
  --server myserver.database.windows.net \
  --database myapp_db \
  --environment production

# Rotate password with verbose logging
./mssql user rotate-password admin_user \
  --server myserver.database.windows.net \
  --database myapp_db \
  --environment dev \
  --verbose

Services

CreateUser

The CreateUser service handles the complete lifecycle of creating a new SQL Server database user with secure password management.

State Functions

1. checkSqlUserExists: Verifies if the user already exists in the database
2. createGcpSecret: Manages password creation/retrieval from GCP Secret Manager
3. createDatabaseUser: Creates the SQL Server login and database user
4. setUserPermissions: Assigns appropriate database permissions

graph TD
    A[Start: checkSqlUserExists] --> B{User exists in DB?}
    B -->|Yes| G[setUserPermissions]
    B -->|No| C[createGcpSecret]
    
    C --> D{Secret exists in GCP?}
    D -->|Yes| E{Secret has value?}
    D -->|No| F[Create new secret]
    E -->|Yes| H[Use existing password]
    E -->|No| I[Create new version]
    F --> J[createDatabaseUser]
    H --> J
    I --> J
    
    J --> K{User created successfully?}
    K -->|Yes| G
    K -->|No, already exists| G
    K -->|Error| L[Return Error]
    
    G --> M{Permissions set?}
    M -->|Yes| N[Success: End]
    M -->|No| L
    
    style A fill:#1565c0,color:#fff
    style N fill:#2e7d32,color:#fff
    style L fill:#c62828,color:#fff

DeleteUser

The DeleteUser service handles the complete lifecycle of deleting an existing SQL Server database user and cleaning up associated password secrets from GCP Secret Manager.

State Functions

1. checkSqlUserExistsDelete: Verifies if the user exists in the database
2. deleteUserFromDatabase: Removes the user from the SQL Server database
3. checkPasswordSecretExists: Checks if the password secret exists in GCP Secret Manager
4. deletePasswordSecret: Removes the password secret from GCP (best-effort cleanup)

graph TD
    A[Start: checkSqlUserExistsDelete] --> B{User exists in DB?}
    B -->|No| C[Check for orphaned secrets]
    B -->|Yes| D[deleteUserFromDatabase]
    
    D --> E{User deleted successfully?}
    E -->|No| L[Return Error]
    E -->|Yes| F[checkPasswordSecretExists]
    C --> F
    
    F --> G{Secret exists in GCP?}
    G -->|No| H[Success: End - No cleanup needed]
    G -->|Error checking| I[Log warning, End - DB cleanup succeeded]
    G -->|Yes| J[deletePasswordSecret]
    
    J --> K{Secret deleted successfully?}
    K -->|No| I
    K -->|Yes| N[Success: End - Complete cleanup]
    
    style A fill:#1565c0,color:#fff
    style H fill:#2e7d32,color:#fff
    style N fill:#2e7d32,color:#fff
    style I fill:#e65100,color:#fff
    style L fill:#c62828,color:#fff

RotatePassword

The RotatePassword service handles the complete lifecycle of rotating passwords for existing SQL Server database users with secure password management and rollback capabilities.

State Functions

1. checkSqlUserExistsRotate: Verifies the target user exists in the database
2. checkPasswordSecret: Validates the existing password secret in GCP Secret Manager
3. createPasswordVersionRotate: Generates a new password and creates a new secret version
4. updateUserPassword: Updates the database user password with rollback on failure
5. cleanupOldVersions: Destroys old password versions, keeping only the latest

graph TD
    A[Start: checkSqlUserExistsRotate] --> B{User exists in DB?}
    B -->|No| L[Return Error: User not found]
    B -->|Yes| C[checkPasswordSecret]
    
    C --> D{Secret exists in GCP?}
    D -->|No| M[Return Error: Secret not found]
    D -->|Yes| E{Latest version accessible?}
    E -->|No| F[Set OldPassword = empty]
    E -->|Yes| G[Set OldPassword = current]
    F --> H[createPasswordVersionRotate]
    G --> H
    
    H --> I[Generate new password]
    I --> J[Create new secret version]
    J --> K[updateUserPassword]
    
    K --> N{DB update successful?}
    N -->|No| O[Rollback: Delete new secret version]
    N -->|Yes| P[cleanupOldVersions]
    O --> L
    
    P --> Q[List all secret versions]
    Q --> R[Sort versions numerically]
    R --> S[Destroy all except latest]
    S --> T[Success: End]
    
    style A fill:#1565c0,color:#fff
    style T fill:#2e7d32,color:#fff
    style L fill:#c62828,color:#fff
    style O fill:#e65100,color:#fff

Testing

The tool includes comprehensive test coverage with mock implementations:

# Run all tests
go test ./...

Make tool accessible from anywhere in your terminal

# build
./zig/zig build mssql

# move
sudo mv ./mssql /usr/local/bin/mssql

# now accessible globally
which mssql