CODEWITHSUNDEEP
sqlsql-serverreporting-servicesuser-permissionsuser-roles
Joanne Chow
Joanne Chow

Reputation: 1048

SQL script to grant user permissions for SQL Server Reporting Services

I know I can do it via ip:port/Reports

Properties -> Security -> New Role Assignment-> "User Name" -> "Roles"

However, I have many databases, each with a different users. So, I would like to automate the process and write an SQL script + double clicking a batch to do the process. I have tried doing insert to users table and userrole table, but it doesn't seem to work, so I suppose it's something about permission granting that I cannot see with my bare eyes... Any hint on what to include in the SQL script?

Upvotes: 1

Views: 7593

Answers (1)

user1578107
user1578107

Reputation: 665

I would not recommend using SQL scripts to modify RS catalog. Schema is undocumented and is quite complicated.

You can use SOAP API calls from RS Scripting Host. Here is the pointer to sample script which assigns permissions http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008%21Script%20Samples%20%28Reporting%20Services%29

Some information about scripting host http://technet.microsoft.com/en-us/library/ms162839.aspx

I also recommend using user groups instead of individual user accounts. It is easier to maintain in the long run.

Upvotes: 3

Related Questions