select on [ReportServer].[dbo].[ExecutionLog3]

Question

select on [ReportServer].[dbo].[ExecutionLog3]

PHXHoward

SSCrazy Eights

Points: 8389
More actions
October 8, 2018 at 5:23 pm

#377932

Hi, how do I give a login access to query ReportServer.dbo.ExecutionLog3 without granting RSExecRole?
grant select on [ReportServer].[dbo].[ExecutionLog3] to [xxx\xxxxxxx] does not really allow selecting the view.
The server principal "xxx\xxxxxxx" is not able to access the database "ReportServer" under the current security context.
Thanks,
Howard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

PHXHoward - Monday, October 8, 2018 5:23 PM
Hi, how do I give a login access to query ReportServer.dbo.ExecutionLog3 without granting RSExecRole?
grant select on [ReportServer].[dbo].[ExecutionLog3] to [xxx\xxxxxxx] does not really allow selecting the view.
The server principal "xxx\xxxxxxx" is not able to access the database "ReportServer" under the current security context.
Thanks,
Howard

The user needs to be granted access to the ReportServer database as well:
CREATE USER <username> FOR LOGIN <loginname>

Sue

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

Thank you. I tried to create the user as you advised but it told me that there was already a user in the ReportServer database that had that name. Checked and sure enough, there was already a user. I tried to drop the user and it said that there was an associated schema. I deleted the schema then deleted the user, then created the user again and granted select on the view. After that it worked.

I'm not sure where the schema and user came from. Possibly it created it when I originally granted select on the view before creating the user.

Howard

Sue_H SSC Guru Points: 90860 More actions · Answer 3

PHXHoward - Tuesday, October 9, 2018 11:17 AM
Thank you. I tried to create the user as you advised but it told me that there was already a user in the ReportServer database that had that name. Checked and sure enough, there was already a user. I tried to drop the user and it said that there was an associated schema. I deleted the schema then deleted the user, then created the user again and granted select on the view. After that it worked.
I'm not sure where the schema and user came from. Possibly it created it when I originally granted select on the view before creating the user.
Howard

You are very welcome. From the first error message, the user wasn't in the database and then with the second error when trying to add the user, I would guess that the user was orphaned.
And then depending on your version of SQL Server, sometimes when you create a user and do not specify the schema, a schema is created with the same name as the user. Sounds like you ran into some odd things like that.
The orphans happen most of the time after restoring a database, not sure if that applies in your case but a possibility.

Sue

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 4

Hi Sue. I just tested again with another login.

grant select on [ReportServer].[dbo].[ExecutionLog3] to [xxx\xxxxx]

The user did not yet exist in the ReportServer database so the system created a new schema and user with the same name. This didn't work as the view is in the dbo security context.

I dropped the new schema and new user and then created the user with the create user statement you provided. The user was created with a default schema of dbo. After that I was able to grant select on the view and it worked correctly.

Thanks again for pointing me in the right direction.