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?
SQL Server 2008 R2 – Security Questions Involving Sys Tables
Securitysql-server-2008-r2
Related Question
- Sql-server – Hidden user rights
- Database Design – Row Level Security vs Multiple Tables
- SQL Server Security – Do Multiple SQL Server Instances Increase Security
- Sql-server – SQL Server 2016 Security – Restrict users from viewing a single database
- SQL Server 2016 Security – User Security Inheritance Explained
- Sql-server – What are SHOWPLAN permission security risks? (MS SQL Server)
Best Answer
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: