Managing Security in SQL Server

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