Manage SQL Server local logins with AWS Secrets Manager. Use this skill when the user wants to: create a SQL Server login and store its credentials as an AWS secret, rotate/change a SQL login password, set up database access for a service account, grant database roles (db_datareader, db_owner, etc.) to a login, or manage SQL Server credentials stored in Secrets Manager. Also use when the user mentions SQL logins + secrets, password rotation for SQL accounts, or provisioning database users with specific permissions.
Manage SQL Server local logins backed by AWS Secrets Manager. This skill bundles two PowerShell scripts that handle the full lifecycle: creating logins with database-level permissions and storing credentials, and rotating passwords with staged secret updates.
#Requires -Version 7secretsmanager:* permissionsThe scripts are bundled in the scripts/ directory relative to this skill file. Resolve the path at runtime:
$skillScripts = Join-Path $PSScriptRoot "scripts"
# Then invoke:
& "$skillScripts/New-SqlLoginWithSecret.ps1" ...
& "$skillScripts/Update-SqlLoginPassword.ps1" ...
If the scripts also exist in the project root, either location works — they are identical.
| Script | Purpose |
|---|---|
scripts/New-SqlLoginWithSecret.ps1 | Create a login, database users, role grants, and AWS secret |
scripts/Update-SqlLoginPassword.ps1 | Rotate password and update the AWS secret (staged rollout) |
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.
| Parameter | Required | Type | Default | Description |
|---|---|---|---|---|
-ServerName | Yes | string | — | SQL Server instance (e.g., SQLSERVER01 or SQLSERVER01\INSTANCE1) |
-LoginName | Yes | string | — | Login name to create |
-Password | No | string | auto-generated 16-char | Password for the login |
-DatabaseName | Yes | string[] | — | One or more databases to create the user in |
-DatabaseRoles | Yes | string[] | — | Roles to grant (see allowed values below) |
-SecretName | No | string | mssql/{server}/{login} | AWS secret name |
-Region | No | string | AWS CLI default | AWS region |
db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_backupoperator, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter
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"
{
"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.
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.
| Parameter | Required | Type | Default | Description |
|---|---|---|---|---|
-SecretId | Yes | string | — | AWS secret name or ARN to rotate |
-Region | No | string | AWS CLI default | AWS region |
./scripts/Update-SqlLoginPassword.ps1 -SecretId "mssql/sqlprod01/svc_reporting"
AWSCURRENT) to get server, login, and existing valuesAWSPENDING via put-secret-value — the current secret is still liveALTER LOGIN to change the password on SQL ServerAWSPENDING to AWSCURRENT via update-secret-version-stage — this is a metadata-only operation so the gap is minimalAWSPREVIOUSIf 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.
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:
| User intent | Roles |
|---|---|
| Read-only / reporting | db_datareader |
| Read-write / application | db_datareader, db_datawriter |
| Full database owner | db_owner |
| DDL changes (schema management) | db_ddladmin |
| Backup operator | db_backupoperator |
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