We have an application user which has been given read write to the appropriate databases. This user has the ability to query the sys tables by default. I've also heard that there are times when this user needs to be able to obtain a listing of tables and columns that exist in the database. Are there any security risks to having the user have read access to the sys tables?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
All users have access to sys tables. Giving read access on the database is orthogonal to accessing the sys tables. System catalogs (ie. sys views and tables) have built in security and you cannot grant, deny or revoke read permissions on them. The visibility of entities in system catalogs derives from the permissions granted on the entity itself, not on the system catalog. To give an example, an user can see a row in sys.tables because it has VIEW DEFINITION permission on that table.
This is laso explained in the MSDN: