Self-service Data Alerts in SSRS 2012 - Learning SQL Server Reporting Services 2012 - page 370

When you click on OK on the Spyglass page (preceding page) and if the SharePoint
administrator has enough permissions to the master database of the service application
then the permissions are updated. In the present case, the computer administrator
administers both SharePoint and the SQL Server 2012 databases, and therefore the
permissions are updated for the SharePoint administrator who is accessing the Data
Alerts feature.
The other two ways are the following:
• From the Spyglass page you can download a script by clicking on the
Download Script button shown (after making sure the SQL Server Agent is
running), and run it on the server which has the three databases previously
mentioned. The download button creates the Spyglass-GrantRights.sql
file shown at the end of this section.
• You can generate the preceding T-SQL script using the two PowerShell
cmdlets shown here:
Get-SPRSDatabaseRightsScript -DatabaseName <ReportingServices
database name> -UserName <app pool account> -IsWindowsUser | Out-
File <path of statement>
Get-SPRSDatabaseRightsScript -DatabaseName ReportingService_46
fd00359f894b828907b254e3f6257c -UserName "NT AUTHORITY\NETWORK
SERVICE" -IsWindowsUser | Out-File c:\SQLServerAgentrights.sql
Again, you need to run this script on the SQL Server 2012 databases as in the
previous method.
Here is the Spyglass-GrantRights.sql file:
/**************************************************************/
/* Copyright (c) Microsoft. All rights reserved. */
/**************************************************************/
USE master
GO
if not exists (select * from sysusers where issqlrole = 1 and name =
'RSExecRole')
BEGIN
EXEC sp_addrole 'RSExecRole'
END
GO