SQL Server 2005 makes all permissions grantable and also makes grantable permissions more granular than in previous versions. Privileges with elevated permissions now include:
CONTROL SERVER permission is new in SQL Server 2005. Change your auditing procedures to include any login with CONTROL SERVER permission.
SQL Server automatically grants the server's Administrators group (BUILTIN\administrators) the sysadmin server role. When running SQL Server 2005 under Microsoft Windows Vista™, the operating system does not recognize membership in the BUILTIN\Administrators group unless the user has elevated themselves to a full administrator. In SP2, you can use SQL Server Surface Area Configuration to enable a principal to act as administrator by selecting Add New Administrator from the main window as shown in Figure 3.
Figure 3 Adding a new administrator in SP2 SQL Server Surface Area Configuration
Clicking on this link opens the SQL Server 2005 User Provisioning Tool for Vista as shown in Figure 4. This tool can also be automatically invoked as the last step of an SQL Server 2005 SP2 installation.
Figure 4 The SQL Server 2005 User Provisioning Tool for Vista
When running SQL Server Express SP2 under the Vista operating system, Set Up incorporates the specification of a specific principal to act as administrator. SQL Server Express SP2 Set Up also allows command-line options to turn user instances on or off (ENABLERANU) and to add the current Set Up user to the SQL Server Administrator role (ADDUSERASADMIN). For more detailed information, see Configuration Options (SQL Server Express) in SQL Server 2005 SP2 Books Online. For additional security-related considerations when running SQL Server 2005 with the Windows Vista operating system, see the SQL Server 2005 SP2 Readme file. In particular, see section 5.5.2 "Issues Caused by User Account Control in Windows Vista."
For accountability in the database, avoid relying on the Administrators group and add only specific database administrators to the sysadmin role. Another option is to have a specific DatabaseAdministrators role at the operating system level. Minimizing the number of administrators who have sysadmin or CONTROL SERVER privilege also makes it easier to resolve problems; fewer logins with administrator privilege means fewer people to check with if things go wrong. The permission VIEW SERVER STATE is useful for allowing administrators and troubleshooters to view server information (dynamic management views) without granting full sysadmin or CONTROL SERVER permission.
Best practices for administrator privileges
Use administrator privileges only when needed.
Minimize the number of administrators.
Provision admin principals explicitly.
Have multiple distinct administrators if more than one is needed.
Avoid dependency on the builtin\administrators Windows group.
Database Ownership and Trust
A SQL Server instance can contain multiple user databases. Each user database has a specific owner; the owner defaults to the database creator. By definition, members of the sysadmin server role (including system administrators if they have access to SQL Server through their default group account) are database owners (DBOs) in every user database. In addition, there is a database role, db_owner, in every user database. Members of the db_owner role have approximately the same privileges as the dbo user.
SQL Server can be thought of as running in two distinct modes, which can be referred to as IT department mode and ISV mode. These are not database settings but simply different ways to manage SQL Server. In an IT department, the sysadmin of the instance manages all user databases. In an Internet service provider environment (say, a Web-hosting service), each customer is permitted to manage their own database and is restricted from accessing system databases or other user databases. For example, the databases of two competing companies could be hosted by the same Internet service provider (ISV) and exist in the same SQL Server instance. Dangerous code could be added to a user database when attached to its original instance, and the code would be enabled on the ISV instance when deployed. This situation makes controlling cross-database access crucial.
If each database is owned and managed by the same general entity, it is still not a good practice to establish a "trust relationship" with a database unless an application-specific feature, such as cross-database Service Broker communication, is required. A trust relationship between databases can be established by allowing cross-database ownership chaining or by marking a database as trusted by the instance by using the TRUSTWORTHY property. An example of setting the TRUSTWORTHY property follows:
ALTER DATABASE pubs SET TRUSTWORTHY ON
Best practices for database ownership and trust
Have distinct owners for databases; not all databases should be owned by sa.
Minimize the number of owners for each database.
Confer trust selectively.
Leave the Cross-Database Ownership Chaining setting off unless multiple databases are deployed at a single unit.
Migrate usage to selective trust instead of using the TRUSTWORTHY property.
SQL Server 2005 introduces schemas to the database. A schema is simply a named container for database objects. Each schema is a scope that fits into the hierarchy between database level and object level, and each schema has a specific owner. The owner of a schema can be a user, a database role, or an application role. The schema name takes the place of the owner name in the SQL Server multi-part object naming scheme. In SQL Server 2000 and previous versions, a table named Employee that was part of a database named Payroll and was owned by a user name Bob would be payroll.bob.employee. In SQL Server 2005, the table would have to be part of a schema. If payroll_app is the name of the SQL Server 2005 schema, the table name in SQL Server 2005 is payroll.payroll_app.employee.
Schemas solve an administration problem that occurs when each database object is named after the user who creates it. In SQL Server versions prior to 2005, if a user named Bob (who is not dbo) creates a series of tables, the tables would be named after Bob. If Bob leaves the company or changes job assignments, these tables would have to be manually transferred to another user. If this transfer were not performed, a security problem could ensue. Because of this, prior to SQL Server 2005, DBAs were unlikely to allow individual users to create database objects such as tables. Each table would be created by someone acting as the special dbo user and would have a user name of dbo. Because, in SQL Server 2005, schemas can be owned by roles, special roles can be created to own schemas if needed—every database object need not be owned by dbo. Not having every object owned by dbo makes for more granular object management and makes it possible for users (or applications) that need to dynamically create tables to do so without dbo permission.
Having schemas that are role-based does not mean that it’s a good practice to have every user be a schema owner. Only users who need to create database objects should be permitted to do so. The ability to create objects does not imply schema ownership; GRANTing Bob ALTER SCHEMA permission in the payroll_app schema can be accomplished without making Bob a schema owner. In addition, granting CREATE TABLE to a user does not allow that user to create tables; the user must also have ALTER SCHEMA permission on some schema in order to have a schema in which to create the table. Objects created in a schema are owned by the schema owner by default, not by the creator of the object. This makes it possible for a user to create tables in a known schema without the administrative problems that ensue when that user leaves the company or switches job assignments.
Each user has a default schema. If an object is created or referenced in a SQL statement by using a one-part name, SQL Server first looks in the user's default schema. If the object isn't found there, SQL Server looks in the dbo schema. The user's default schema is assigned by using the CREATE USER or ALTER USER DDL statements. If the default schema is specified, the default is dbo. Using named schemas for like groups of database objects and assigning each user's default schema to dbo is a way to mandate using two-part object names in SQL statements. This is because objects that are not in the dbo schema will not be found when a one-part object name is specified. Migrating groups of user objects out of the dbo schema is also a good way to allow users to create and manage objects if needed (for example, to install an application package) without making the installing user dbo.
Best practices for using schemas
Group like objects together into the same schema.
Manage database object security by using ownership and permissions at the schema level.
Have distinct owners for schemas.
Not all schemas should be owned by dbo.
Minimize the number of owners for each schema.