invisible views

Question

invisible views

padigan

SSC Rookie

Points: 34
More actions
August 7, 2013 at 6:14 am

#296762

I'm looking after a SQL 2008 R2 box and several users have suddenly encountered a problem in a particular database, all views under a particular schema are now invisible however they previously were visible to these users. Strangely they can still select from these views if they can remember the names of the views. They can still see all views under the 'dbo' schema though fine.
After going through their permissions on the accounts, they all have 'data reader' permissions and no 'deny' permissions. I've created a test user and given it only 'data reader' permissions and logged in to the server using that account, as suspected it can see all the views and all works perfectly.
I'm at a loss as to why this is happening, I could have understood if the test user had the same problem but its fine 🙂
I'm sure its probably something daft in front of my eyes but has anyone got a clue as to what I'm missing? This is bangin my head now!! :w00t:

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

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 1

It is likely to be something to do with the VIEW DEFINITION permission. If the users don't have it or have it explicitly denied on the schema then that could be the issue. If the users are part of a group in Windows that also has a user in the database, or if they belong to a database role, then it could be that which has the permission issue. Try granting the permission to one of the users on the schema. If that doesn't work, then it could be explicitly denied elsewhere, either on the view or a group/role that the user belongs to.

To troubleshoot, you can use the function fn_my_permissions, (link) from the user's point of view:

EXECUTE AS LOGIN = '<login name here>'

SELECT * FROM fn_my_permissions ( '<two-part view name here>' , 'object' )

REVERT

Hope that helps..

Duncan

padigan SSC Rookie Points: 34 More actions · Answer 2

Hi Duncan

Thanks for the reply, I'd already tried that and for the schema, table, database and views the permissions between the test user and the problem user were exactly the same. However I've now just fixed the problem, here's what it was...

The schema in question was actually owned by another user who had for some reason gone crazy and added herself loads of permissions incorrectly and she'd basically ticked the boxes for membership of all the database roles including the 'deny data reader', she'd also taken ownership of all the schema's etc... once I turned the custom schema owner back to dbo the problem user could then see the views again (but he could actually select from them anyway when they were invisible!!! mad!! :hehe:)

However I still cannot understand why the test user could actually see the views originally and the problem user could not. The only difference was that the test user was an SQL account and the problem user was a windows account. Maybe that had some bearing on it? Who knows

This is a definitely a strange one for me and it sounds almost like an SQL Server bug or maybe a 'quirk' :w00t:

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 3

Glad you got it sorted out - sounds like you had fun 😀

There will be a good reason why the user saw the behaviour they did. SQL Server handles permissions very reliably and according to a strict set of rules. Understanding how those rules work together can be tricky at times though.

Duncan