Set‑SqlDscServerPermission

SYNOPSIS

Sets exact server permissions for a principal.

SYNTAX

Login

Set-SqlDscServerPermission -Login <Login> [-Grant <SqlServerPermission[]>]
 [-GrantWithGrant <SqlServerPermission[]>] [-Deny <SqlServerPermission[]>] [-Force]
 [-WhatIf] [-Confirm] [<CommonParameters>]

ServerRole

Set-SqlDscServerPermission -ServerRole <ServerRole> [-Grant <SqlServerPermission[]>]
 [-GrantWithGrant <SqlServerPermission[]>] [-Deny <SqlServerPermission[]>] [-Force]
 [-WhatIf] [-Confirm] [<CommonParameters>]

DESCRIPTION

This command sets the exact server permissions for a principal on a SQL Server Database Engine instance. The permissions passed in will be the only permissions set for the principal - any existing permissions not specified will be revoked.

The principal can be specified as either a Login object (from Get-SqlDscLogin) or a ServerRole object (from Get-SqlDscRole).

This command internally uses Get-SqlDscServerPermission, Grant-SqlDscServerPermission, Deny-SqlDscServerPermission, and Revoke-SqlDscServerPermission to ensure the principal has exactly the permissions specified.

EXAMPLES

EXAMPLE 1

$serverInstance = Connect-SqlDscDatabaseEngine
$login = $serverInstance | Get-SqlDscLogin -Name 'MyLogin'
Set-SqlDscServerPermission -Login $login -Grant ConnectSql, ViewServerState

Sets the exact granted permissions for the login 'MyLogin'. Any other granted permissions will be revoked.

EXAMPLE 2

$serverInstance = Connect-SqlDscDatabaseEngine
$login = $serverInstance | Get-SqlDscLogin -Name 'MyLogin'
Set-SqlDscServerPermission -Login $login -Grant ConnectSql -GrantWithGrant AlterAnyDatabase -Deny ViewAnyDatabase

Sets exact permissions for the login 'MyLogin': grants ConnectSql, grants AlterAnyDatabase with grant option, and denies ViewAnyDatabase. Any other permissions will be revoked.

EXAMPLE 3

$serverInstance = Connect-SqlDscDatabaseEngine
$role = $serverInstance | Get-SqlDscRole -Name 'MyRole'
$role | Set-SqlDscServerPermission -Grant @() -Force

Revokes all granted permissions from the role 'MyRole' without prompting for confirmation.

PARAMETERS

-Deny

Specifies the permissions that should be denied. The permissions specified will be the exact denied permissions - any existing denied permissions not in this list will be revoked. If this parameter is omitted (not specified), existing Deny permissions are left unchanged.

Type: SqlServerPermission[]
Parameter Sets: (All)
Aliases:
Accepted values: AdministerBulkOperations, AlterAnyAvailabilityGroup, AlterAnyConnection, AlterAnyCredential, AlterAnyDatabase, AlterAnyEndpoint, AlterAnyEventNotification, AlterAnyEventSession, AlterAnyEventSessionAddEvent, AlterAnyEventSessionAddTarget, AlterAnyEventSessionDisable, AlterAnyEventSessionDropEvent, AlterAnyEventSessionDropTarget, AlterAnyEventSessionEnable, AlterAnyEventSessionOption, AlterAnyLinkedServer, AlterAnyLogin, AlterAnyServerAudit, AlterAnyServerRole, AlterResources, AlterServerState, AlterSettings, AlterTrace, AuthenticateServer, ConnectAnyDatabase, ConnectSql, ControlServer, CreateAnyDatabase, CreateAnyEventSession, CreateAvailabilityGroup, CreateDdlEventNotification, CreateEndpoint, CreateLogin, CreateServerRole, CreateTraceEventNotification, DropAnyEventSession, ExternalAccessAssembly, ImpersonateAnyLogin, SelectAllUserSecurables, Shutdown, UnsafeAssembly, ViewAnyCryptographicallySecuredDefinition, ViewAnyDatabase, ViewAnyDefinition, ViewAnyErrorLog, ViewAnyPerformanceDefinition, ViewAnySecurityDefinition, ViewServerPerformanceState, ViewServerSecurityAudit, ViewServerSecurityState, ViewServerState

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Force

Specifies that the permissions should be set without any confirmation.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Grant

Specifies the permissions that should be granted. The permissions specified will be the exact granted permissions - any existing granted permissions not in this list will be revoked. If this parameter is omitted (not specified), existing Grant permissions are left unchanged.

Type: SqlServerPermission[]
Parameter Sets: (All)
Aliases:
Accepted values: AdministerBulkOperations, AlterAnyAvailabilityGroup, AlterAnyConnection, AlterAnyCredential, AlterAnyDatabase, AlterAnyEndpoint, AlterAnyEventNotification, AlterAnyEventSession, AlterAnyEventSessionAddEvent, AlterAnyEventSessionAddTarget, AlterAnyEventSessionDisable, AlterAnyEventSessionDropEvent, AlterAnyEventSessionDropTarget, AlterAnyEventSessionEnable, AlterAnyEventSessionOption, AlterAnyLinkedServer, AlterAnyLogin, AlterAnyServerAudit, AlterAnyServerRole, AlterResources, AlterServerState, AlterSettings, AlterTrace, AuthenticateServer, ConnectAnyDatabase, ConnectSql, ControlServer, CreateAnyDatabase, CreateAnyEventSession, CreateAvailabilityGroup, CreateDdlEventNotification, CreateEndpoint, CreateLogin, CreateServerRole, CreateTraceEventNotification, DropAnyEventSession, ExternalAccessAssembly, ImpersonateAnyLogin, SelectAllUserSecurables, Shutdown, UnsafeAssembly, ViewAnyCryptographicallySecuredDefinition, ViewAnyDatabase, ViewAnyDefinition, ViewAnyErrorLog, ViewAnyPerformanceDefinition, ViewAnySecurityDefinition, ViewServerPerformanceState, ViewServerSecurityAudit, ViewServerSecurityState, ViewServerState

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-GrantWithGrant

Specifies the permissions that should be granted with the grant option. The permissions specified will be the exact grant-with-grant permissions - any existing grant-with-grant permissions not in this list will be revoked. If this parameter is omitted (not specified), existing GrantWithGrant permissions are left unchanged.

Type: SqlServerPermission[]
Parameter Sets: (All)
Aliases:
Accepted values: AdministerBulkOperations, AlterAnyAvailabilityGroup, AlterAnyConnection, AlterAnyCredential, AlterAnyDatabase, AlterAnyEndpoint, AlterAnyEventNotification, AlterAnyEventSession, AlterAnyEventSessionAddEvent, AlterAnyEventSessionAddTarget, AlterAnyEventSessionDisable, AlterAnyEventSessionDropEvent, AlterAnyEventSessionDropTarget, AlterAnyEventSessionEnable, AlterAnyEventSessionOption, AlterAnyLinkedServer, AlterAnyLogin, AlterAnyServerAudit, AlterAnyServerRole, AlterResources, AlterServerState, AlterSettings, AlterTrace, AuthenticateServer, ConnectAnyDatabase, ConnectSql, ControlServer, CreateAnyDatabase, CreateAnyEventSession, CreateAvailabilityGroup, CreateDdlEventNotification, CreateEndpoint, CreateLogin, CreateServerRole, CreateTraceEventNotification, DropAnyEventSession, ExternalAccessAssembly, ImpersonateAnyLogin, SelectAllUserSecurables, Shutdown, UnsafeAssembly, ViewAnyCryptographicallySecuredDefinition, ViewAnyDatabase, ViewAnyDefinition, ViewAnyErrorLog, ViewAnyPerformanceDefinition, ViewAnySecurityDefinition, ViewServerPerformanceState, ViewServerSecurityAudit, ViewServerSecurityState, ViewServerState

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Login

Specifies the Login object for which the permissions are set. This parameter accepts pipeline input.

Type: Login
Parameter Sets: Login
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-ServerRole

Specifies the ServerRole object for which the permissions are set. This parameter accepts pipeline input.

Type: ServerRole
Parameter Sets: ServerRole
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

CommonParameters

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.

INPUTS

Microsoft.SqlServer.Management.Smo.Login

Accepts input via the pipeline.

Microsoft.SqlServer.Management.Smo.ServerRole

Accepts input via the pipeline.

OUTPUTS

None.

NOTES

The Login or ServerRole object must come from the same SQL Server instance where the permissions will be set. If specifying -ErrorAction 'SilentlyContinue' then the command will silently continue if any errors occur. If specifying -ErrorAction 'Stop' the command will throw an error on any failure.

Important

This command only modifies permission categories that are explicitly specified. If you omit a parameter (e.g., don't specify -Grant), permissions in that category are left unchanged. However, if you specify a parameter (even as an empty array like -Grant @()), the command sets exact permissions for that category only - revoking any permissions not in the list. This allows you to independently manage Grant, GrantWithGrant, and Deny permissions without affecting the other categories.

RELATED LINKS