Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

SQL SSRS report error

When users try to excute a report they get the following error

An error has occured during report processing . (rsprocessingAborted)  Query excution failed for dataset 'JobPlanSummaryDataSet. (rsErrorExecutingCommand)
The EXECUTE permision was denied on the object 'GetJobPlanReport' database 'JobPlan.net, Schema 'dbo'
0
GSLElectric
Asked:
GSLElectric
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yeah ... and ....

In the report that was executed, one of your datasets calls an object (Function, Stored Procedure) called GetJobPlanReport, which the user executing the report did not have execute
privs on.

Usually there's a role assigned to all SSRS executors, then users are assigned to that role.
Something like..
-- ROLES AND USERS 

-- DROP everything
IF NOT EXISTS (select 1 from sys.database_principals where name='role' and Type = 'R')
   DROP ROLE [SSRSRole] 

If not Exists (select loginname from master.dbo.syslogins where loginname = 'domain_name\SSRSUsers')
   DROP USER [domain_name\SSRSUsers] 

-- Add role
CREATE ROLE [SSRSRole] AUTHORIZATION [dbo]

-- Add User
CREATE USER [domain_name\SSRSUsers] FOR LOGIN [domain_name\SSRSUsers] WITH DEFAULT_SCHEMA=[dbo]

-- Assign role to user
EXEC sp_addrolemember 'SSRSRole', 'domain_name\SSRSUsers'


-- GRANT PRIVS ON ALL INDIVIDUAL OBJECTS TO ROLE

GRANT EXECUTE ON dbo.your_sp_name TO [domain_name\SSRSUsers] AS [dbo]

Open in new window

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now