When you install reporting server, you have in general two options available – either you install the reporting server and configure its database at the same time, or install just reporting service and point it to an existing database later.
We had recently opportunity to configure server to point to existing reporting database. We encountered a security related issue, because the account we were using to connect to the SQL Server database engine was not ‘good enough’ to configure reporting service connectivity. Namely, the installation fails if you are not a sysadmin, security admin, or dbo on master and msdb databases.
So what is happening if you are configuring reporting server to point to existing database? This is how it seems to work in SQL Server 2008 R2:
First, new role called RSExecRole is added to master database if it doesn’t exist:
1 use master
2 go
3 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
4 BEGIN
5 EXEC sp_addrole 'RSExecRole'
6 END
7
8 go
Then do the same in the msdb database:
1 use msdb
2
3 go
4
5 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
6 BEGIN
7 EXEC sp_addrole 'RSExecRole'
8 END
9 go
And in reporting database and the temp database for reporting:
1 USE [ReportServer$SQL1]
2
3 go
4
5 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
6 BEGIN
7 EXEC sp_addrole 'RSExecRole'
8 END
9
10 go
11
12 USE [ReportServer$SQL1TempDB]
13 go
14
15 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
16 BEGIN
17 EXEC sp_addrole 'RSExecRole'
18 END
19
20 go
21
Next the reporting server configuration manager is mapping user to the login we want to use in each of the databases, interestingly in the order as below:
1
2 USE [ReportServer$SQL1]
3
4 go
5
6
7 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
8 BEGIN
9 if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
10 BEGIN
11 EXEC sp_grantdbaccess N'RSUser'
12 END
13 END
14
15 go
16
17
18 USE [ReportServer$SQL1TempDB]
19
20 go
21
22
23 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
24 BEGIN
25 if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
26 BEGIN
27 EXEC sp_grantdbaccess N'RSUser'
28 END
29 END
30
31 go
32
33
34 USE [msdb]
35
36 go
37
38
39 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
40 BEGIN
41 if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
42 BEGIN
43 EXEC sp_grantdbaccess N'RSUser'
44 END
45 END
46
47 go
48
49 USE [master]
50 go
51
52 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
53 BEGIN
54 if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
55 BEGIN
56 EXEC sp_grantdbaccess N'RSUser'
57 END
58 END
59
60 GO
61
Eventually the user is added to the RSExecRole:
1
2 USE [ReportServer$SQL1]
3 go
4
5 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
6 BEGIN
7 EXEC sp_addrolemember 'RSExecRole', N'RSUser'
8 END
9
10 go
11
12
13 USE [msdb]
14 go
15
16 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
17 BEGIN
18 EXEC sp_addrolemember 'RSExecRole', N'RSUser'
19 END
20 go
21
22 USE [master]
23 go
24
25 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
26 BEGIN
27 EXEC sp_addrolemember 'RSExecRole', N'RSUser'
28 END
29
30 go
31
32
33 USE [ReportServer$SQL1TempDB]
34 go
35
36 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
37 BEGIN
38 EXEC sp_addrolemember 'RSExecRole', N'RSUser'
39 END
40
41
Ha. I was about to give a rant about implementation of sp_addrolemember system procedure, which in SQL 2005 is using sysusers and other obsolete objects. The version in SQL Server 2008 R2 has been rewritten from what I see, and is using sys.database_principals in its logic. Well done Microsoft 
.