To help you understand the information presented in this chapter, we begin by defining some security-related terms. This brief list of definitions is not intended to serve as a comprehensive SQL Server glossary. It is provided as a quick reference to help you understand some of the security elements discussed throughout this chapter. You may find it useful to refer back to this list of security definitions as you read through this chapter and through Chapter 9, "Managing Security."
-
alias
-
An alias is a database username that is shared by several login IDs. A database alias allows you to treat more than one person as the same user inside a database, giving all of them the same permissions. Any username in a database can also serve as an alias.
Aliases are often used so that several users can assume the role of database owner.
-
database object owner
-
Database objects are tables, indexes, views, defaults, triggers, rules, and procedures. The user who creates a database object is the database object owner and is automatically granted all permissions on it. The database object owner can grant permission to other users to use that object. Database object ownership cannot be transferred.
-
database owner
-
The database owner (DBO) is the creator of a database. There is only one DBO. The DBO has full privileges inside the database that he or she owns, and determines the access and capabilities provided to other users.
In his or her own database, the user is recognized as DBO; in other databases, the database owner is known by his or her database username.
DBO status can be reassigned to a different user. Only one login ID can be DBO, although other login IDs can be aliased to DBO.
-
domain
-
In Windows NT security, a domain is a collection of computers that are grouped for viewing and administrative purposes, and that share a common security database.
-
group
-
In SQL Server, a database group is a collection of database users. The users receive the database permissions granted to the group. Using groups simplifies management of a large number of database users, because groups provide a convenient way to grant and revoke permissions to more than one user at the same time.
In Windows NT, a group is a collection of Windows NT users. The users received the Windows NT rights and permissions granted to the group. Groups provide a convenient way to manage the capabilities of a large number of users with similar needs, within the security scope of a domain or a computer.
-
integrated security
-
Integrated security allows a SQL Server to use Windows NT authentication mechanisms to validate logins for all connections. Only trusted (multi-protocol or named pipes) connections are allowed.
-
login ID
-
A login ID is a name by which a user is known to SQL Server. Login IDs are also referred to as logins.
To log in to a SQL Server that is running standard security, a user must provide a valid login ID and password.
-
login security mode
-
The login security mode determines the manner in which a SQL Server validates a login request. There are three types of login security: integrated, standard, and mixed.
-
mixed security
-
Mixed security allows login requests to be validated using either integrated or standard security. Trusted connections (as used by integrated security) and nontrusted connections (as used by standard security) can be established.
-
object permissions
-
Object permissions regulate the use of certain statements on certain database objects. They are granted and revoked by the owner of the object.
-
permissions
-
Microsoft SQL Server uses permissions to enforce database security. The SQL Server permissions system specifies which users are authorized to use which Transact-SQL statements, views, and stored procedures. The ability to assign permissions is determined by each user's status (as SA, database owner, or database object owner).
There are two types: object permissions and statement permissions.
-
standard security
-
Standard security uses SQL Server's own login validation process for all connections. To log in to a SQL Server, each user must provide a valid login ID and password.
-
statement permissions
-
Statement permissions provide the privilege to issue certain Transact-SQL statements. Statement permissions are not object-specific. They can be granted only by the SA or the database owner.
Statement permissions apply to these statements: CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP DATABASE, and DUMP TRANSACTION.
Statement permissions are also called command permissions.
-
system administrator
-
The system administrator (SA) is the person responsible for the administrative and operational functions that are independent of any particular application, and is likely to be a person with a comprehensive overview of SQL Server and all its applications.
Administering SQL Server typically includes such tasks as installing SQL Server; configuring servers and clients; managing and monitoring the use of disk space, memory, and connections; creating devices and databases; authorizing SQL Server users and granting them permissions; transferring data in and out of SQL Server databases; backing up and restoring databases; implementing and maintaining replication; scheduling unattended operations; monitoring and tuning SQL Server performance; and diagnosing system problems. The system administrator may also advise application designers about the data that already exists on SQL Server, make recommendations about standardizing data definitions across applications, and so on.
The system administrator operates outside the protection system, which means that SQL Server does no permission checking for the system administrator. The system administrator is also treated as the owner of whatever database he or she is using. Anyone who knows the SA password can log in and act as system administrator (unless the server is running in Integrated login security mode).
-
trusted connections
-
Integrated security requires network protocols that support authenticated connections between clients and servers. These are referred to as trusted connections. The multi-protocol and named pipe protocols provide trusted connections.
Note that nontrusted connections (connections over other network protocols that do not support authenticated connections) must be handled by using SQL Server standard security.
-
username
-
In SQL Server, a database username is a name assigned to a login ID for the purpose of allowing a user to have access to a database. The abilities a user has within a database depend on the permissions granted to the username (and to any groups the username is a member of).
In Windows NT, a username is the name by which the user is known to the domain, or to an individual Windows NT computer. A user logs on to a domain or computer by providing a valid username and password. The abilities of the user depend on the Windows NT rights and permissions granted to that username and to any Windows NT groups the username is a member of.