Understanding Access Control
The basis of security for your SQL Server is this: Users should have
appropriate access to the data they need, no more and no less. In other
words, users should not have too much access to too much data.
Consider the following:
. Most users need to read and write data from tables, but few
users will ever need to be able to create and drop tables.
. Some users might need to read tables but might not need to
update them.
. You might want to allow users to add data, but not delete data.
. Some users (managers or administrators) might need rights to
manipulate user accounts, but most should not.
. You might want users to access data via stored procedures, but
never directly.
. You might want to restrict access to some functionality based on
where the user is logging in.
These are just examples, but they help demonstrate an important point.
You need to provide users with the access they need and just the access they need. This is known as access control, and managing access control
requires creating and managing user accounts.

When first installed, SQL Server creates a user account named sa (for System
Administrator), which has complete and total control over the entire SQL
Server. You might have been using the sa login throughout the lessons in
this book, and that is fine when experimenting with SQL Server on nonlive
servers. But in the real world you’d never use sa on a day-to-day
basis. Instead, you’d create a series of accounts, some for administration,
some for users, some for developers, and so on.

NOTE: Preventing Innocent Mistakes
It is important to note that access control is not just intended to
keep out users with malicious intent. More often than not, data
nightmares are the result of an inadvertent mistake, a mistyped
T-SQL statement, being in the wrong database, or some other user
error. Access control helps avoid these situations by ensuring that
users are unable to execute statements they should not be
executing.

CAUTION: Don’t Use sa
The sa login should be considered sacred. Use it only when
absolutely needed (perhaps if you cannot get in to other administrative
accounts). sa should never be used in day-to-day SQL Server
operations.

Web Tech Vision 2007