Page 96 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes
Figure 6.3

The user account in each database is used to apply security permissions for the objects (for
example, tables, views, and stored procedures) in that database. This user account can be mapped
from Microsoft Windows NT® 4.0 and Windows® 2000 user accounts, Windows NT 4.0 and
Windows 2000 groups in which the user is a member, or SQL Server login accounts. If there is no
account mapped directly, the user may be allowed to work in a database under the guest account,
if one exists. The activities a user is allowed to perform are controlled by the permissions
applied to the user account from which they gained access to a database.

Notes Security Note When possible, use Windows Authentication. Also, avoid using the
guest account; all logins without their own database permissions obtain the database
permissions granted to this account. If you must use the guest account, grant it minimum
permissions.
SQL Server accepts commands after a user gains access to a database. All activities a user
performs in a database are communicated to SQL Server through Transact-SQL statements.
When an instance of SQL Server receives a Transact-SQL statement, it ensures the user has
permission to execute the statement in the database. If the user does not have permission to
execute a statement or access an object used by the statement, SQL Server returns a permissions
error.

6.3 Disadvantages of SQL Server Authentication

If a user is a Windows domain user who has a login and password for Windows, he must
still provide another (SQL Server) login and password to connect. Keeping track of multiple
names and passwords is difficult for many users. Having to provide SQL Server credentials
every time that one connects to the database can be annoying.

SQL Server Authentication cannot use Kerberos security protocol.
Windows offers additional password policies that are not available for SQL Server
logins.

6.4 Advantages of SQL Server Authentication

Allows SQL Server to support older applications and applications provided by third parties
that require SQL Server Authentication.
Allows SQL Server to support environments with mixed operating systems, where all
users are not authenticated by a Windows domain.

90 LOVELY PROFESSIONAL UNIVERSITY