How can we assign permissions to setup SQL Server Reporting Services

Question

In SQL Server 2012, how can we give a permission to someone to have full control to SSRS and a permission to a specific Db so they can configure and run the SSRS reports and have a full control to backup/restore their Databases but do not have control to other databases on the same SQL Server machine?

Answer 1

Lets say you create a sql user login called sqllogin, Leave this user's Server roles as public.

Under Login Properties section for this user in User Mapping select the one database you want them against (in the list on rightside) and choose the following roles

db_backupoperator
db_datareader
db_datawriter
db_ddladmin

Note: not all options are ticked in image.

This allows user login only to tempdb (for this example) and not the others. They might be able to see other databases but cannot access them in anyway.

More info on roles here

As far as SSRS is concerned, create a datasource mapped to this database with this sqllogin.This is not the same as permission to SSRS but only to the database they get access to.

SSRS permissions are different, I'm pretty sure the sql login cannot be used. This might be of help Authentication SSRS.

But I hope this gets you started.