I am checking whether a user has a permission to view the report through dataset stored procedure and if the user doesn't, raiserror is called.
Is there a way to display a different message from SQL Server Reports 2005 when a stored procedure (that populates report dataset) raises error (through RAISERROR) instead of returning data?
Here is a skeleton code of dataset stored procedure
create procedure ReportSprocName
@ClientID int,
@Login sysname
as
begin
--; check user's permission through @Login
--; * Pseudo code *
if @Login does not have permission begin
raiserror(@Login does not have permission, 127, 1)
return
end
select id, name, etc...
from someTable
end
GO
I am interested in two possibly solutions
- Displaying error message within RAISERROR
- Displaying a hardcoded custom message in SSRS report itself
Currently this is the default message displayed by SSRS reports
Best Answer
Why would you allow the user fire to the report if they don't have permission to view it?
To display a custom message in the SSRS report itself:
Provide an expression for the Visibility > Hidden value (Properties Window). IE:
= iif( count(Fields!Application_Number.Value, "YOUR-DATASOURCE-NAME") > 0, true, false)