Everyone has access

Question

Everyone has access

Kyle Sherry

SSChasing Mays

Points: 606
More actions
April 7, 2017 at 2:30 pm

#329360

I've deleted out all the users and groups from report server and even went as far as deleting them all from the Users table. However, anyone in the organization is still able to access the web port reporting services. If I login to a user who isn't even apart of anything technical they get added into the "Users" table and have full access. I've searched online for quite some time and haven't found anything really relating as to what might be causing this? Below is an image of what the security page "SITE SETTINGS" looks like. I'm needing to lock this down to only specific users in the organization can access it and then limit what folders each one of them have access to. Any ideas or directions to look into? It is a new install of SSRS 2016. Thank you in advance for any advice or help!

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Just curious if all of the users might be getting added to the local admins group or even a domain admins group (don't laugh I have seen it).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Kyle Sherry SSChasing Mays Points: 606 More actions · Answer 2

SQLRNNR - Friday, April 7, 2017 3:48 PM
Just curious if all of the users might be getting added to the local admins group or even a domain admins group (don't laugh I have seen it).

Not laughing I appreciate the response. To answer your question no they aren't apart of the local admins group. I read that even if the the BUILTIN/Administrator account is deleted the local admins group on the server will still have access so I already checked there. I also know they are not apart of the admins domain group. I appreciate any suggestions to investigate!

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Kyle Sherry - Friday, April 7, 2017 3:52 PM
Not laughing I appreciate the response. To answer your question no they aren't apart of the local admins group. I read that even if the the BUILTIN/Administrator account is deleted the local admins group on the server will still have access so I already checked there. I also know they are not apart of the admins domain group. I appreciate any suggestions to investigate!

Try running the following and see what permissions are set in SSRS - still may not give you the answer but worth checking what permissions are setup at the site, folder, item levels:
SELECT u.UserName, r.RoleName, r.[Description], c.[Path], c.Name FROM dbo.PolicyUserRole ur INNER JOIN dbo.Policies B on ur.PolicyID = B.PolicyID INNER JOIN dbo.Users u on ur.UserID = u.UserID INNER JOIN dbo.Roles r on ur.RoleID = r.RoleID INNER JOIN dbo.Catalog c on ur.PolicyID = c.PolicyID ORDER BY u.UserName

Sue

Kyle Sherry SSChasing Mays Points: 606 More actions · Answer 4

Sue_H - Sunday, April 9, 2017 8:52 PM
Kyle Sherry - Friday, April 7, 2017 3:52 PM
Not laughing I appreciate the response. To answer your question no they aren't apart of the local admins group. I read that even if the the BUILTIN/Administrator account is deleted the local admins group on the server will still have access so I already checked there. I also know they are not apart of the admins domain group. I appreciate any suggestions to investigate!
Try running the following and see what permissions are set in SSRS - still may not give you the answer but worth checking what permissions are setup at the site, folder, item levels:
SELECT u.UserName, r.RoleName, r.[Description], c.[Path], c.Name FROM dbo.PolicyUserRole ur INNER JOIN dbo.Policies B on ur.PolicyID = B.PolicyID INNER JOIN dbo.Users u on ur.UserID = u.UserID INNER JOIN dbo.Roles r on ur.RoleID = r.RoleID INNER JOIN dbo.Catalog c on ur.PolicyID = c.PolicyID ORDER BY u.UserName
Sue

When I run this it shows the specific users and folders they have permissions to as "Browser" which is exactly how I want it. However, they can still view any folder they want. I also notice any user who I randomly ask to login that has nothing to even do with any IT group or Admin group automatically get added into the Users table. Thank you so much for the help. I'm going to work on it today as well if I find solution I'll post. Hopefully there are more ideas out thrown out.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Kyle Sherry - Monday, April 10, 2017 7:57 AM
When I run this it shows the specific users and folders they have permissions to as "Browser" which is exactly how I want it. However, they can still view any folder they want. I also notice any user who I randomly ask to login that has nothing to even do with any IT group or Admin group automatically get added into the Users table. Thank you so much for the help. I'm going to work on it today as well if I find solution I'll post. Hopefully there are more ideas out thrown out.

A couple of other things you may want to look at - check the account setup for Reporting Services in Report Server Configuration Manager and check the members of the role created for Reporting Server Services.

Sue

Kyle Sherry SSChasing Mays Points: 606 More actions · Answer 6

Sue_H - Monday, April 10, 2017 8:11 AM
Kyle Sherry - Monday, April 10, 2017 7:57 AM
When I run this it shows the specific users and folders they have permissions to as "Browser" which is exactly how I want it. However, they can still view any folder they want. I also notice any user who I randomly ask to login that has nothing to even do with any IT group or Admin group automatically get added into the Users table. Thank you so much for the help. I'm going to work on it today as well if I find solution I'll post. Hopefully there are more ideas out thrown out.
A couple of other things you may want to look at - check the account setup for Reporting Services in Report Server Configuration Manager and check the members of the role created for Reporting Server Services.
Sue

The account setup for report services configuration manager under "Service Account" is "Virtual Service Directory". I was trying to read to understand the different account types but I'm not convinced that is the issue. Description of virtual account below.

"Virtual Service Account represents the windows service. It is a built-in least-privilege account that has network logon permissions. This account is recommended if you do not have a domain user account available or if you want to avoid any service disruptions that might occur as a result of password expiration policies."

Sue_H SSC Guru Points: 90860 More actions · Answer 7

Kyle Sherry - Monday, April 10, 2017 9:08 AM
The account setup for report services configuration manager under "Service Account" is "Virtual Service Directory". I was trying to read to understand the different account types but I'm not convinced that is the issue. Description of virtual account below.

"Virtual Service Account represents the windows service. It is a built-in least-privilege account that has network logon permissions. This account is recommended if you do not have a domain user account available or if you want to avoid any service disruptions that might occur as a result of password expiration policies."

No that wouldn't be an issue. I should have specified to look if you have it setup for a specific domain user account. That account and the Report Services group would really be my only other guesses with a normal native default installation.
The most likely issue I've seen when this happens is exactly what Jason posted and I have seen authenticated users added to the admins group. Other than that, it's typically group added to the site level. Just seems like it would be more of something at the OS level since that is primarily where the authentication takes place for the kernel part that handles Reporting Services.
I don't have any other ideas. One thing I'd probably want to try to see if it's something where any user has the same access as an administrator is to create an account, try logging into the server and if successful try accessing things that only an admin could access or what they can do. It might help narrow things down as to where the issue is - OS or SSRS.

Sue