System metadata refers to information such as the number and names of tables or views in a database, information about columns in a table, details on constraints and indexes defined for a table, users and login information, and so on. Many applications and scripts access this system metadata information for various purposes. SQL Server 2000 supported rich metadata exposure through SQL-92 INFORMATION_SCHEMA views, system tables at the server and database level, and system stored procedures. However, there were some limitations, such as the following:
Metadata access and management has been completely redesigned and rethought in SQL Server 2005. The following section explains a new system database called the Resource database and the role it plays. Next, you will learn about how the new security architecture restricts access to system metadata. Finally, the following section discusses the concept of schemas, which is a major change in the SQL Server security architecture.
The Resource Database
The SQL Server 2005 database rengine makes use of a new system database called the Resource database (with database ID 32767). This is a hidden, read-only database that contains all the system objects that are included with SQL Server 2005. These system objects were present in the master database in previous releases. The Resource database consists of definitions of system stored procedures, views, functions, assemblies, and so on. The system objects are persisted in the resource database, but they logically appear in every database. These system objects are present in a schema named sys. (The concept of schemas is discussed later in this chapter.) The Resource database does not show up in graphical tools or when you access the sys.databases catalog view (discussed later in this chapter). The resource database files (that is, mssqlsystemresource.mdf and mssqlsystemresource.ldf) are present in the same folder where the master database files reside.
Do not move or rename the Resource database file, or SQL Server will not start. Also, do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so hinders performance and prevents upgrades.
Note that the Resource database does not contain any user data or user metadata. The system-level information for an instance of SQL Server is still saved in the master database. Therefore, it is not necessary to backup the Resource database (unless some changes are made to the Resource database to apply a Quick Fix Engineering (QFE) based on instructions from a Microsoft Customer Support Services specialist).
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions, upgrading involved dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is accomplished by copying the single Resource database file to the local server. This copy method also means that all that is required to roll back a service pack is an overwrite operation of the current version of the Resource database with the identified older version.
You can access the ResourceVersion and ResourceLastUpdateDateTime server properties to determine the Resource database version and the date and time that the Resource database was last updated:
SELECT SERVERPROPERTY('ResourceVersion'); SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
The only way to access the Resource database is to start SQL Server in single-user admin mode (using the -m startup parameter) and then run the USE mssqlsystemresource SQL statement. After you have connected to a SQL Server 2005 instance in single-user admin mode and changed the database context to Resource database by running the USE mssqlsystemresource SQL statement, you can then directly query the system base tables, such as master.sys.sysdbreg, in the master database or the sys.sysidxstats system table in any user database. You need to ensure that the Resource database is the current database in the context, and then you can query system tables in any user database or in the master database. Note that these system tables are not the ones available in previous releases. The system tables from previous releases have been replaced with backward-compatibility views, as explained in the next section. These system base tables are used by the SQL Server 2005 database engine and by Microsoft personnel during the troubleshooting process.
When DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that running DBCC CHECKDB on the master database can return extra results. The command returns extra result sets when no options are set or when either the PHYSICAL ONLY or ESTIMATE ONLY option is set.
Metadata Access and Visibility
The new system metadata architecture in SQL Server 2005 is designed to meet the following goals:
To meet these design goals, SQL Server 2005 introduces the changes described in the following sections.
Introducing Catalog Views
All the system tables available in previous releases are now shipped as backward-compatibility views, and their use is discouraged. SQL Server 2005 introduces the notion of catalog views, and using them is the preferred way to access system metadata. There are four different types of views in SQL Server 2005: catalog views, backward-compatibility views, DMVs, and information schema views. Using catalog views is the most efficient and recommended approach to access system metadata, and it is the only way to access metadata for new features such as Service Broker. System tables available in previous releases have been completely removed, and views having the same names as the system tables are provided for backward compatibility. The backward-compatibility views and information schema are not available for new features introduced in SQL Server 2005.
There are about 286 system metadata views, which include catalog views, DMVs, information schema views, and backward-compatibility views. You can run the following query to obtain a list of all the system metadata views available in SQL Server 2005:
SELECT * FROM sys.all_views WHERE is_ms_shipped = 1 ORDER BY [name];
All the system metadata objects belong to the sys or INFORMATION_SCHEMA schemas. Catalog views use a naming convention wherein sys.% views describe a user's metadata, sys.system_% describes system objects, sys.all_% is the union of system objects and user objects, and sys.server_% views describe server-level metadata. Consider the following SQL statements:
USE [AdventureWorks]; SELECT * FROM sysobjects ORDER BY [name]; SELECT * FROM dbo.sysobjects ORDER BY [name]; SELECT * FROM sys.sysobjects ORDER BY [name]; SELECT * FROM sys.objects ORDER BY [name]; SELECT * FROM sys.all_objects ORDER BY [name]; SELECT * FROM sys.system_objects ORDER BY [name];
The first SELECT statement should look familiar. However, note that sysobjects is no longer a system table; rather, it is a backward-compatibility view. The next two statements continue to use the sysobjects backward-compatibility view but illustrate that you can use the dbo or sys schema while accessing the backward-compatibility views. The next SELECT statement uses the sys.objects catalog view, which returns a list of user-defined objects and base system tables present in the database. The sys.all_objects catalog view returns system objects in addition to user-defined objects. These system objects are persisted in the Resource database but are accessible in every database. Finally, the sys.system_objects catalog view returns only the system objects.
Catalog views are designed using the inheritance model, where the base or parent view (such as sys.objects) contains a subset of columns and a superset of rows, while the derived or child views (such as sys.tables and sys.views) return a superset of columns and a subset of rows. The inheritance hierarchy is illustrated in Figure 2.2.
Figure 2.2. The catalog views design is based on the inheritance model.
Try out the following SQL statements and notice that sys.objects returns a subset of columns and a superset of rows, whereas sys.tables, sys.views, and sys.procedures return a superset of columns but a subset of rows:
USE AdventureWorks; SELECT * FROM sys.objects; SELECT * FROM sys.tables; SELECT * FROM sys.procedures; SELECT * FROM sys.views;
SQL Server 2005 implements a metadata catalog security system that is similar in spirit to what the ANSI SQL-99 specification calls for. The basic idea is pretty simple: You can see metadata for objects you have access to; you get back an empty set for objects you don't have access to. A new security layer has been added over the persisted system metadata. All the metadata access mechanisms go through this security layer. Therefore, if a user does not have permission on an object, metadata information for that object is not returned when the catalog is accessed.
For instance, let's say a user X is created in the AdventureWorks sample database and no permissions are assigned to this user. If this user X TRies to access the sys.objects catalog view, the sys.sysobjects backward compatibility view, or the INFORMATION_SCHEMA.TABLES view, an empty result set is returned. Let's now assume that user X is granted SELECT or VIEW DEFINITION permission on the Sales.Store table. Now, when the same metadata query is run by user X, the results containing metadata for Sales.Store are returned. In other words, metadata information is filtered and made visible only on a need-to-know basis; that is, catalog security returns rows for objects on which the user has some permission. This is a huge change from previous releases, where metadata information was available to all users, regardless of whether they had permission on the object.
Note that sa has access to all the systemwide metadata, and dbo has access to all the databasewide metadata. Also, some metadata is accessible by all database users. Typically, this is for things such as filegroups that have no grantable permissions. Therefore, anyone can query the sys.filegroups catalog view and obtain the database filegroups information. Figure 2.3 shows the new metadata architecture.
Figure 2.3. A new security layer on top of persisted metadata ensures that the metadata access mechanism, such as catalog views, backward-compatibility views, and so on, get the filtered rows based on permissions assigned to the user.
More details on metadata security can be found in Chapter 7, "SQL Server 2005 Security."
The allow updates Option
Because direct updates to system tables are not supported, the system configuration option allow updates is meaningless in SQL Server 2005.
Userschema separation is another important change to the security and management architecture of SQL Server. As per the ANSI SQL-92 standard, the purpose of a schema is to act like a namespace to group related database entities and to avoid name collisions. For instance, if you want to group related objects, such as all the sales objects or all the human resources objects, or if you want to have two objects with the same name, the SQL-92 standard's answer is to use database schemas.
In earlier versions of SQL Server, a database user was used to form a namespace and to avoid name collision. The name of the owner of the object was used, along with the object name, to uniquely identify an object (for example userX.table1, userY.table1). This approach of treating users as schemas caused of lot of database management headaches. For instance, let's assume that you wanted to drop a user. Before you did that, either you had to drop all the objects that the user owned or assign the object ownership to a different user. Dropping objects was often not a practical solution, and changing the owner often required change in the application (for example, the application had to be updated to access userY.table1 instead of userX.table1). Applications often used the dbo user as the object owner, but that approach had its own problems (as discussed in Chapter 7).
SQL Server 2005 breaks the tight coupling between users and schemas, and it does not treat users as schemas. SQL Server 2005 introduces support for database schemas as per the SQL-92 standard. Therefore, object names in SQL Server 2005 no longer follow the <owner_name>.<object_name> pattern; rather, object names in SQL Server 2005 are <schema_name>.<object_name>, where <schema_name> is created by using the CREATE SCHEMA DDL statement, and schemas are owned by database users. Database users who have schema creation permissions can create schemas by using CREATE SCHEMA DDL statements. Database users who have object creation permissions can create objects in a schema. Now, if you have to drop a user, all that is required to be done is to just assign the schemas that the user owns to other users. No application change is required because the application will continue to access the objects as <schema_name>.<object_name>, no matter who the schema owner is. Also, instead of changing the object ownership of hundreds of database objects, as before, now you have to just change the ownership of very few database schemas. Figure 2.4 shows the old and new approaches to naming objects.
Figure 2.4. SQL Server 2005 introduces true support for database schemas, simplifying the security and management architecture.
Every database user has a default schema that is used for name resolution. If you do not specify one, SQL Server 2005 assigns dbo as the default schema for the new user. When the object is not fully qualified, SQL Server looks for the object in the user's default schema. If SQL Server does not find the object, it looks for the object in the dbo schema.
Userschema separation is discussed in detail in Chapter 7.