An Overview of SQL Server's Security Model
SQL Server's security model comprises the following components:
- SQL Server login
- Database user
- guest user
- Permissions
- Roles
We will overview each of these in this article.
SQL Server Login
The SQL Server login model supports two security modes:
- Windows Authentication
- Mixed Security
| CAUTION |
|
Did you know that by default the sa account is not password protected! After
installing SQL Server we recommend
that you immediately change the sa password.
|
Windows Authentication
Windows Authentication takes
advantage of Windows NT user security and account mechanisms. This security
mode allows SQL Server to share the username and password used for Windows NT
and allows the user to bypass the SQL Server login process. Users with a valid
Windows NT account can log in to SQL Server without supplying a username and
password.
Some benefits of Windows Authentication are as follows:
- A user does not have to remember a separate password
and username.
- When the password changes in Windows NT, the user does not have to change the
password in SQL Server.
How does Windows Authentication work? When a user accesses SQL Server, SQL Server
obtains the user and password information from the user's NT network security
attributes. These attributes are established when the user logs in to Windows
NT. If the user has been granted access to SQL Server, the user is
automatically logged in to SQL Server. Using Windows Authentication allows you to take
advantage of Windows NT features such as password aging and login auditing.
Windows Authentication requires more NT hands-on experience or working closely with
the NT system administrator when setting up user accounts and groups. Setting up
Windows Authentication requires a few more steps than setting up SQL Server
Authentication, but the benefits outweigh the additional configuration steps.
| TIP |
| With Windows Authentication we recommending placing users into NT
groups and adding the
NT group login to SQL Server. This powerful feature allows you to group your
users together at the NT Domain level and apply permissions at the SQL Server
level. For example a group named sales could exist in the NT Domain. A
new login based on the NT sales group could be added to SQL Server.
When a new NT account is created the NT administrator can add the new account
to the NT group named sales. The SQL Server DBA can apply the
appropriate role(s) and permissions to the sales group. The new NT
login automatically contains the necessary permissions to log in to SQL
Server because the NT sales group was previously granted a login. This
strategy makes it easy for new users to be added the system.
|
Mixed Security
In mixed mode security,
both Windows Authentication and
SQL Server Authentication are enabled. When using SQL Server Authentication, an
individual logging in to SQL Server must supply a username and a password that
SQL Server validates against a system table. When using Windows Authentication (see the
earlier section "Windows Authentication" for more information), users can log in to SQL
Server without being prompted for a login ID and password.
| NOTE |
| Did you ever wonder where logins are stored in SQL Server? They are stored in the
master..syslogins table.
|
Database User
The database user concept defines the database(s) an individual can access.
After an individual has successfully logged in to SQL Server, either through Windows
Authentication or SQL Server Authentication, SQL Server
determines whether the user is a valid user for the database he is accessing.
Regardless of the security mode, a user must be permitted to access the
database. If the user is not permitted in the database, SQL Server returns an
error message.
The only exception to the database user concept is the guest
user. See the next topic for more information on the guest user.
guest User
A special username, guest, can be added to a database to
allow anyone with a valid SQL Server login to access the database. The guest
username is a member of the public role. After the guest user has
been added to a database, any individual with a valid SQL Server
login[md]regardless of security mode[md]can access the database as the guest
user. A guest user works as follows:
- SQL Server checks to see whether the login ID has a valid username or alias
assigned. If so, SQL Server grants the user access to the database as
the username or aliases. If not, go to step 2.
- SQL Server checks to see whether a guest username exists. If so, the login
ID is granted access to the database as guest. If the guest account does
not exist, SQL Server denies access to the database.
| NOTE |
The guest user always has an uid of 2.
A guest user is added to the master database and the pubs
database when the system is installed. SQL Server prevents you from dropping
the guest user from the master database so that you cannot
accidentally do so. If you removed guest from the master
database, only the sa user could log in to SQL Server! When users log in
to SQL Server, they have access to the master database as the guest
user. (Don't worry, the guest user has very few permissions in the
master
database.)
To prevent guest access in any database other than master,
drop the guest account from the corresponding database.
|
IE 7
Firefox 2.0
