Lesson 2: Understanding the Authorization Process

3 4

After SQL Server 2000 has authenticated a user, the user can perform only the administrative tasks and access only the user databases that have been specifically authorized (permitted). In this lesson, you will learn the variety of methods by which an authenticated user receives permissions. First, you will learn about server roles (sometimes referred to as fixed server roles), which grant permissions to perform server-wide tasks. Next, you will be introduced to database-specific permissions, including database owner permissions, database roles, statement permissions, object permissions, and application roles.

---

After this lesson, you will be able to

• Describe the server roles
• Describe the fixed and user-defined database roles
• Describe database owner permissions
• Describe statement and object permissions

Estimated lesson time: 15 minutes

---

Understanding Server-Wide Permissions

SQL Server 2000 provides a number of predefined server roles with associated administration permissions. These server roles grant server-wide permissions to perform various tasks and include permissions that you can only grant to users through the use of these server roles. You cannot delete server roles and cannot change their permissions. To grant a user these permissions, you add their login to the server role. With Transact-SQL, you can add a Windows user or group to a server role without first creating a login (SQL Server 2000 will create the login automatically).

Note

---

Server roles are identical in concept to built-in groups in Windows 2000.

Table 10.2 describes the eight server roles that are available with SQL Server 2000.

Table 10.2 Server Roles in SQL Server 2000

Server Role	Members of This Server Role Can ...
sysadmin	Perform any task within a SQL Server 2000 instance and within any database. By default, all members of the Windows built-in Administrators group, as well as the sa SQL Server security account, belong to this server role.
serveradmin	Configure SQL Server 2000 using the sp_configure system stored procedure and can shut down the SQL Server service. Members of the Windows built-in Server Operators group map well to this server role.
setupadmin	Install and configure linked servers, remote servers, and replication. Can also mark a stored procedure for execution at startup, such as sp_serveroption. Members of the Windows built-in Server Operators group map well to this server role.
securityadmin	Perform all security-related operations within SQL Server 2000, including managing CREATE DATABASE statement permissions, controlling server logins, and reading the SQL Server error log. Help desk personnel and members of the Windows built-in Server Operators group map well to this server role.
processadmin	Manage processes running in an instance of SQL Server. Can kill user processes, such as runaway queries. Help desk personnel map well to this server role.
dbcreator	Can create, modify, and delete databases. Senior database administrators who are not members of the sysadmin server role map well to this server role.
diskadmin	Can manage disk files and backup devices. Generally only used for backward compatibility with SQL Server 6.x.
bulkadmin	Can execute BULK INSERT statements. Allows members of the sysadmin server role to delegate BULK INSERT tasks without granting sysadmin rights. Use carefully because members must also have read access to any data being inserted and INSERT permission on any table into which data will be inserted.

Note

---

A member of any server role can add other users to that server role.

Understanding Database-Specific Permissions

Access to SQL Server 2000 does not (by itself) grant a user access to any of the databases within SQL Server 2000. In addition, except for membership in the sysadmin role, membership in a server role does not grant any database-specific permissions. Database access rights must be specifically granted by a system administrator or by a member of an administrator role in the database.

Permissions can be granted, denied, or revoked, and include the right to create objects, administer the database, execute certain Transact-SQL statements, insert data to a table, or simply view data within a view. SQL Server 2000 has a number of mechanisms for granting users specific permissions within a database. The various database-specific permissions are described in Table 10.3.

Table 10.3 Database-Specific Permissions

Permission	Description
Database owner	A user can be specified as the owner of the database, and can perform any activity with respect to the database.
DBO role	All members of the sysadmin server role are automatically members of the dbo role within each database, and can perform any activity with respect to the database.
User	Specified users and groups can be granted user access to a database via their Windows 2000 or SQL Server 2000 security account. A permitted database user is then granted permissions within the database through a database role, the public role, and specific grants of statement and object permissions.
Guest user	An authenticated user who has access to an instance of SQL Server 2000 (but who does not have a user account to access a particular database) can be permitted to access a database as a guest user. The guest account can be granted specific permissions within the database (generally to read certain data). By default, a database does not have a guest user account.
Public role	All users permitted to access a database become members of the public role within each database. The public role can be granted specific permissions (generally permissions needed by all users of the database).
Fixed database role	Permitted users can be added to fixed database roles within a database. Fixed database roles contain predefined rights within a database to perform database-wide activities.
User-defined database role	Permitted users can be added to user-defined database roles within a database. These roles can be created by an administrator and granted specifically delineated rights and permission within the database.
Statement permissions	Permission to execute administrative statements (such as CREATE PROCEDURE) can be granted, revoked, or denied to users, groups, and roles.
Object permissions	Permission to access database objects (such as a table or view) can be granted, revoked, or denied to users, groups, and roles.
Application role	Permission to perform specific activities within a database can be granted to an application, rather than granted to a user. An application connects to a database and activates the application role. Users accessing a database through this connection gain the permissions associated with the application role for the duration of the connection. Permissions assigned to a particular user are irrelevant when the user is accessing the database through an application role.

Fixed Database Roles

Each database contains nine predefined database roles with associated database-wide permissions to perform various tasks. You cannot delete these database roles and cannot change their permissions. To grant a user these permissions within a database, you add the user's database user account to the database role. If these fixed database roles do not grant the combination of rights you need, you can create user-defined database roles with custom rights (generally more restrictive rights).

Table 10.4 describes the nine fixed database roles that are available with SQL Server 2000.

Table 10.4 Fixed Database Roles in SQL Server 2000

Database Role	Members of This Database Role Can ...
db_owner	Perform any task within a SQL Server 2000 database. Members of this role have the same rights as the database owner and the members of the dbo role.
db_accessadmin	Add or remove Windows 2000 or Windows NT 4.0 users and groups and SQL Server users in a database (using the sp_grantdbaccess system stored procedure).
db_securityadmin	Manage all permissions, roles, role membership, and object ownership in a database (using the GRANT, REVOKE, and DENY statements).
db_ddladmin	Add, modify, or drop objects in the database (using the CREATE, ALTER, and DROP statements).
db_backupoperator	Run DBCC commands, issue checkpoints, and back up the database (using the DBCC, CHECKPOINT, and BACKUP Transact-SQL statements).
db_datareader	Read data from any user table or view in the database (you have SELECT permission on all tables and views).
db_datawriter	Modify or delete data from any user table or view in the database (you have INSERT, UPDATE, and DELETE permissions on all tables and views).
db_denydatareader	Not read data from any user table in the database (you do not have SELECT permission on any objects). Can be used with the db_ddladmin role to allow an administrator to create database objects owned by the dbo role, but not be able to read sensitive data contained in those objects.
db_denydatawriter	Not modify or delete data from any user table in the database (you do not have INSERT, UPDATE, and DELETE permissions on any object).

Lesson Summary

In addition to permitting users access to SQL Server 2000, a user must be granted authority to perform actions within SQL Server 2000. Server roles are used to grant different levels of rights to perform server-wide administration functions. Within a database, you can grant users (and groups) database-wide administration rights using database roles. They can also be granted statement and object permissions. User-defined roles can be created with customized rights with respect to statements and objects. Finally, guest user access can be permitted with certain rights and the public role can be used to grant general rights to all authorized users.