Security

Set-sqmBackupExcludePermission

Grants SELECT, INSERT, and UPDATE permissions on master.dbo.sqm_BackupExclude to a specified Windows group or SQL login — creating the login and the master database user automatically if they do not yet exist.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (Medium)
Output: PSCustomObject

Execution Flow

START sqm_BackupExclude exists? throw — run Sync first Yes Login exists? No New-DbaLogin (create login) Already exists DB user in master? No New-DbaDbUser in master Already exists GRANT SELECT, INSERT, UPDATE ON master.dbo.sqm_BackupExclude Return PSCustomObject (Status=Success) DONE
Requires master.dbo.sqm_BackupExclude to exist — run Sync-sqmBackupExcludeTable first. All three steps (create login, create user, grant) honor ShouldProcess, so -WhatIf shows the full planned chain without executing it.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-LoginNameStringRequiredWindows group (e.g. DOMAIN\DBA-Team) or SQL login to grant permissions to.
-EnableExceptionSwitchOptionalThrows terminating errors instead of logging a warning.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject with: SqlInstance, LoginName, LoginCreated, UserCreated, PermissionsGranted, Status, Message.

Examples

Example 1 — Grant permissions to a Windows group

Set-sqmBackupExcludePermission -SqlInstance "SQL01" -LoginName "CONTOSO\DBA-Team"

Example 2 — Preview without changes

Set-sqmBackupExcludePermission -SqlInstance "SQL01" -LoginName "CONTOSO\DBA-Team" -WhatIf