SQL Login Manager

If the scripts also exist in the project root, either location works — they are identical.

Creating a Login

Use New-SqlLoginWithSecret.ps1 to create a SQL Server login, map it as a database user in one or more databases, grant fixed database roles, and store the credentials in AWS Secrets Manager.

Parameters

Allowed database roles

db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_backupoperator, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter

Examples

Read-only service account across two databases:

./scripts/New-SqlLoginWithSecret.ps1 -ServerName "SQLPROD01" -LoginName "svc_reporting" `
    -DatabaseName "SalesDB","AnalyticsDB" -DatabaseRoles "db_datareader"

Full-access application account with explicit password and custom secret name:

./scripts/New-SqlLoginWithSecret.ps1 -ServerName "SQLPROD01\DEV" -LoginName "app_user" `
    -Password "MyP@ssw0rd!" -DatabaseName "AppDB" `
    -DatabaseRoles "db_datareader","db_datawriter" `
    -SecretName "mssql/dev/app-user" -Region "us-east-1"

What it creates

1. SQL login on master (idempotent — skips if login already exists)
2. Database user in each database mapped to the login (idempotent)
3. Role membership for each role in each database (idempotent)
4. AWS secret with this JSON structure:

{
  "engine": "sqlserver",
  "server": "SQLPROD01",
  "port": 1433,
  "username": "svc_reporting",
  "password": "<generated-or-provided>",
  "databases": "SalesDB,AnalyticsDB"
}

The script errors if a secret with the same name already exists — pass a different -SecretName or delete the existing secret first.

Rotating a Password

Use Update-SqlLoginPassword.ps1 to generate a new password, update the SQL Server login, and update the AWS secret with minimal downtime between the SQL change and the secret becoming live.

Parameters

Example

./scripts/Update-SqlLoginPassword.ps1 -SecretId "mssql/sqlprod01/svc_reporting"

Rotation sequence

1. Retrieves the current secret (AWSCURRENT) to get server, login, and existing values
2. Generates a new 16-character cryptographically secure password
3. Stages the new secret as AWSPENDING via put-secret-value — the current secret is still live
4. Runs ALTER LOGIN to change the password on SQL Server
5. Promotes AWSPENDING to AWSCURRENT via update-secret-version-stage — this is a metadata-only operation so the gap is minimal
6. The previous password remains accessible as AWSPREVIOUS

If step 4 (ALTER LOGIN) fails, the script automatically rolls back the AWSPENDING version and the SQL login keeps its original password. If step 5 fails, the script warns that the SQL password was changed but the secret promotion failed — the new password would need to be set manually.

Common Patterns

Gathering parameters from the user: When the user's request is vague (e.g., "set up a service account"), ask for the specific values needed — server name, login name, which databases, and what level of access (read-only, read-write, full owner). Map their intent to the appropriate database roles:

Secret naming: The default pattern is mssql/{server}/{login} with the server name lowercased and backslashes replaced by hyphens. Suggest a custom -SecretName when the user has a specific naming convention.

Running the tests: The project includes Pester integration tests at tests/SqlLoginWithSecret.Tests.ps1 that cover both creation and rotation against a live SQL Server. Run with:

Invoke-Pester ./tests/SqlLoginWithSecret.Tests.ps1 -Output Detailed