SSRS – Grant Execute to All Stored Procedures

You need to grant execute permissions on all stored procedures in the database without explicitly knowing what those procedures are called.  A classic example for SQL Server Reporting Services is for your embedded Data Source credential to access procedures in your Reporting database. You have created your login on SQL Server, typed the login and password in the SSRS Data Source and now need to grant execute on all stored procedures in the database.  Use the following code to grant execute on all stored procedures…


USE ReportingDatabase

GO

CREATE ROLE db_executerole

GO

GRANT EXECUTE TO db_executerole

GO

EXEC sp_addrolemember 'db_executerole', 'ReportingorActiveDirectoryLogin'

GO

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s