Using Application Roles in SQL Server Reporting Services

Application Roles are a great concept, but apparently MS “forgot” to implement support for them in some places, such as DataSets. Recently I had to figure out how to use application roles in SSRS, and since I did not find anything useful on the net, I’ll share my findings here, hoping this will save someone else some time.

As always, once you figured it out, it’s easy. This is the code that needs to go into each (!) DataSet; just insert your original SQL statament where indicated:

DECLARE @cookie varbinary(8000);
DECLARE @fCreateCookie bit
SELECT @fCreateCookie = 1
EXEC sp_setapprole 'AppRoleName', 'Password', @fCreateCookie = @fCreateCookie , @cookie = @cookie OUTPUT;
EXEC sp_unsetapprole @cookie;

This is the basic code that you would find in any samples, the big difference is that I declared two variables that have the same name as the parameters to sp_setapprole. This causes SSRS to not treat the parameters as report parameters.

No comments:

adaxas Web Directory