THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

Insecure Metadata

I was looking at some metadata access today and this reminded me of when I talked about this topic two years ago at the PASS Summit in Denver.


Back in SQL Server 2000 most of the existing metadata was available to members of the public role, that is, to any login that can connect to the SQL Server instance. SQL Server 2005 promises that a user can only see the metadata of an object the user owns or has access to. This was a huge security improvement. However, some items were still left available to any user, which in some cases could be a security concern.


Let us test some of it. Create a new login called insecure without granting any permission to it. Connect as that user and see what kind of information from the server you are able to obtain. Try this first


select * from sys.databases


This new user is able to see the list of all the databases on the server, including the ones he does not have access to. Try


select * from sys.configurations


This time you can see some configuration information of your instance including some specific security information like if the ‘c2 audit mode’ is enabled.


Some other data available to any SQL Server user is information about the location of the files of the current database, linked servers or backup devices. Try these


select * from sys.database_files

select * from sys.servers

select * from sys.backup_devices


Here I am using catalog views only but the same applies if you use the SQL Server 2000 compatibility views (like sysdatabases). You can also execute some stored procedures like these to get similar information


sp_helpdb 'master'

exec sp_configure

exec sp_helpfile

exec sp_helpserver

exec sp_helpdevice


Now let us look at the msdb database. By looking at the following system tables (yes, these are called system tables) you can obtain all the information regarding backup and restore operations performed on your instance. You can obtain information like database names, location of databases files, locations of database backup files and names of users who performed these backups.


use msdb


select * from backupfile

select * from backupmediafamily

select * from backupset

select * from restorefile

select * from restorehistory


You can also run some extended stored procedures or functions like the following


exec master..xp_msver

select serverproperty('ProductVersion')

select serverproperty('IsIntegratedSecurityOnly')


For example, the first two display the version (build) of SQL Server, which can show if the database administrator has installed the latest service pack or security updates. This can reveal some known security vulnerability of your instance.


Perhaps you can see now that this information, like security configuration, database names, database file names, location of database backups, user names, etc. could be used by an attacker to get additional permissions or data.


Certainly these are the defaults and many of them can be changed to be more secure, but many times this is not practical as some tools and applications depend on these defaults. For example, you can revoke the VIEW ANY DATABASE permission (executed as member of sysadmin) to secure the information displayed on sys.databases.


revoke view any database to public


But now only database owners can see their databases listed on sys.databases. Of course, this is a problem for a valid user who is not a database owner, who will only see the master and tempdb databases on this list. To test, grant db_datareader permissions to the insecure user to any user database. Test this again connected as the insecure login


select * from sys.databases


Only the master and tempdb databases are listed this time. One known issue of this configuration is that you can not use ODBC Data Source Administrator to create a new data source to connect to a database you have access to (unless you are the database owner)





Even Management Studio does not list all the databases you have access to (but you can use the USE statement)






But what is still ironic is that, even when you can not use ODBC Data Source Administrator or list your databases in Management Studio, you can still use db_name() to list all the database names anyway like in


select db_name(5)


Finally, note that a command introduced with SQL Server 2005, VIEW DEFINITION, could also help you to have more control on the access to SQL Server metadata.

Published Wednesday, November 18, 2009 2:00 AM by Ben Nevarez
Filed under: ,
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Privacy Statement