THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Piotr Rodak

if datepart(dw, getdate()) in (6, 7)
    use pubs;



Setting reporting database–what happens behind the scenes

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 Smile.

 

 

 

 

Published Sunday, November 28, 2010 3:30 PM by Piotr Rodak

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement