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:

BEGIN
DECLARE @cookie varbinary(8000);
DECLARE @fCreateCookie bit
SELECT @fCreateCookie = 1
EXEC sp_setapprole 'AppRoleName', 'Password', @fCreateCookie = @fCreateCookie , @cookie = @cookie OUTPUT;
-- PASTE YOUR SQL HERE
EXEC sp_unsetapprole @cookie;
END

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.