Set‑SqlDscServerPermission - dsccommunity/SqlServerDsc GitHub Wiki
Sets exact server permissions for a principal.
Set-SqlDscServerPermission -Login <Login> [-Grant <SqlServerPermission[]>]
[-GrantWithGrant <SqlServerPermission[]>] [-Deny <SqlServerPermission[]>] [-Force]
[-WhatIf] [-Confirm] [<CommonParameters>]
Set-SqlDscServerPermission -ServerRole <ServerRole> [-Grant <SqlServerPermission[]>]
[-GrantWithGrant <SqlServerPermission[]>] [-Deny <SqlServerPermission[]>] [-Force]
[-WhatIf] [-Confirm] [<CommonParameters>]
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.
$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.
$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.
$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.
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: FalseSpecifies 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: FalseSpecifies 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: FalseSpecifies 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: FalseSpecifies 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: FalseSpecifies 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: FalsePrompts 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: FalseShows 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: FalseThis cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.
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.