Request‑SqlDscRSDatabaseRightsScript

SYNOPSIS

Generates the T-SQL script to grant database permissions for Reporting Services.

SYNTAX

Request-SqlDscRSDatabaseRightsScript [-Configuration] <Object> [-DatabaseName] <String> [-UserName] <String>
 [-IsRemote] [-UseSqlAuthentication] [<CommonParameters>]

DESCRIPTION

Generates the T-SQL script to grant the necessary database permissions for a user account to access the report server database. This is done by calling the GenerateDatabaseRightsScript method on the MSReportServer_ConfigurationSetting CIM instance.

The generated script can be executed on a SQL Server Database Engine instance using Invoke-SqlDscQuery to grant the required permissions.

The configuration CIM instance can be obtained using the Get-SqlDscRSConfiguration command and passed via the pipeline.

EXAMPLES

EXAMPLE 1

Get-SqlDscRSConfiguration -InstanceName 'SSRS' | Request-SqlDscRSDatabaseRightsScript -DatabaseName 'ReportServer' -UserName 'NT SERVICE\SQLServerReportingServices'

Generates the T-SQL script to grant permissions on the 'ReportServer' database for the Reporting Services service account.

EXAMPLE 2

$config = Get-SqlDscRSConfiguration -InstanceName 'SSRS'
$serviceAccount = $config.WindowsServiceIdentityActual
$script = $config | Request-SqlDscRSDatabaseRightsScript -DatabaseName 'ReportServer' -UserName $serviceAccount
Invoke-SqlDscQuery -ServerName 'localhost' -InstanceName 'RSDB' -DatabaseName 'master' -Query $script -Force

Gets the Reporting Services service account from the configuration object, generates the database rights script, and executes it on the RSDB SQL Server instance.

EXAMPLE 3

$config = Get-SqlDscRSConfiguration -InstanceName 'SSRS'
Request-SqlDscRSDatabaseRightsScript -Configuration $config -DatabaseName 'ReportServer' -UserName 'DOMAIN\SQLRSUser' -IsRemote

Generates the rights script for a remote database scenario.

PARAMETERS

-Configuration

Specifies the MSReportServer_ConfigurationSetting CIM instance for the Reporting Services instance. This can be obtained using the Get-SqlDscRSConfiguration command. This parameter accepts pipeline input.

Type: Object
Parameter Sets: (All)
Aliases:

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

-DatabaseName

Specifies the name of the report server database to grant permissions on. This should match the database name used when creating the database.

Type: String
Parameter Sets: (All)
Aliases:

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

-IsRemote

If specified, indicates that the database is on a remote server. By default, assumes the database is local.

When specified with Windows authentication (default), the UserName must be in the format <domain>\<username>. See the Microsoft documentation for more information about username requirements: https://learn.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/configurationsetting-method-generatedatabaserightsscript

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-UserName

Specifies the user account name to grant permissions to. This is typically the Reporting Services service account.

Type: String
Parameter Sets: (All)
Aliases:

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

-UseSqlAuthentication

If specified, indicates the user is a SQL Server authentication user. By default, assumes Windows authentication.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
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.Management.Infrastructure.CimInstance

Accepts MSReportServer_ConfigurationSetting CIM instance via pipeline.

OUTPUTS

System.String

Returns the T-SQL script as a string.

NOTES

This command should be run after creating the report server database using the script from Request-SqlDscRSDatabaseScript. After granting permissions, use Set-SqlDscRSDatabaseConnection to configure the Reporting Services instance to use the database.

To get the Reporting Services service account name, use the WindowsServiceIdentityActual property from the configuration object: (Get-SqlDscRSConfiguration -InstanceName 'SSRS').WindowsServiceIdentityActual

RELATED LINKS

https://docs.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/configurationsetting-method-generatedatabaserightsscript