0

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?

nam
  • 21,967
  • 37
  • 158
  • 332
  • Will the User use SQL login or NTLM and did you want them to alter the data, `table, procedure, views`..etc ? – Searching Oct 27 '16 at 20:00
  • @Searching SQL Login with full control on his/her database and be able to configure and run SSRS reports for his/her database. But no access to the databases of other users. – nam Oct 27 '16 at 20:55

1 Answers1

0

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

login properties

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

permission example

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.

sample

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.

Searching
  • 2,269
  • 1
  • 17
  • 28