Microsoft SQL Server's Security Model

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

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.

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.

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:

1. 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.
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.