thedavegray (10/1/2014)
heh I know what you mean. I have been in at least one place where the intentionally gave me totally bizarre stuff like this to see how I would respond.
Hmm, hope not - you could be right though. 😀
Just had a look at some of the views and it seems like they are pulling data from various schemas and also different databases in the instance and also from linked servers. I will politely ask the reason behind this request though.
The other thing they have asked to do is to lock down the informational_schema, I've denied select on this however it does not seem to stop the user selecting from it. I did read that it was controlled by the public role however I cannot see any reference to this schema at the database level.
I was wondering if perhaps the views were pulling external data. In this case it would make sense to restrict access there. But denying access to InformationSchema is a bit strange. How would a normal user ever be able to query that stuff directly? If they are supposed to working with sql they kind of need that in order to do their job. It is difficult to write decent queries if you can't at least view the table definitions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/