The Database Engine’s security system includes two different security subsystems:
- Windows security
- Database Engine security
Windows security specifies security at the operating system level—that is, the method by which a user connects to Windows using her Windows user account (called “user account” in the rest of the book). Authentication using this subsystem is also called Windows authentication.
Database Engine security specifies the additional security necessary at the system level— that is, how users who have already logged on to the operating system can subsequently connect to the database server. Database Engine security defines a SQL Server login (called “login” in the rest of the book) that is created within the system and is associated with a password. Some logins are identical to the existing user accounts. (Authentication using this subsystem is called SQL Server authentication.)
Based on these two security subsystems, the Database Engine can operate in one of the following authentication modes:
- Windows mode
- Mixed mode
Windows mode requires users to use user accounts exclusively to log in to the system. The system accepts the user account, assuming it has already been validated at the operating system level. This kind of connection to a database system is called a trusted connection, because the Database Engine trusts that the operating system already validated the account and the corresponding password.
Mixed mode allows users to connect to the Database Engine either using Windows authentication or SQL Server authentication. This means that some user accounts can be set up to use the Windows security subsystem, while others can be set up to use both the Database Engine security subsystem and the Windows security subsystem.
NOTE SQL Server authentication is provided for backward compatibility only. For this reason, use Windows authentication instead.
You use SQL Server Management Studio to choose one of the existing authentication modes. To set up Windows mode, right-click the server and click Properties. In the Server Properties dialog box, choose the Security page and click Windows Authentication Mode. To choose Mixed mode, the only difference is that you have to click SQL Server and Windows Authentication Mode in the Server Properties dialog box.
1. Setting Up the Database System Security
The security of the database system can be set up using:
- SQL Server Management Studio
- T-SQL statements
The following subsections discuss these two alternatives.
1.1. Managing Security Using SQL Server Management Studio
To create a new login using Management Studio, expand the server, expand Security, right-click Logins, and click New Login. The Login dialog box (see Figure 12-3) appears. First, you have to decide between Windows authentication and SQL Server authentication. If you choose Windows authentication, the login name must be a valid Windows name, which is written in the form domain\user_name. If you choose SQL Server authentication, you have to type the new login name and the corresponding password. Optionally, you may also specify the default database and language for the new login. (The default database is the database that the user is automatically connected to immediately after logging in to the Database Engine.) After that, the user can log in to the system under the new account.
1.2. Managing Security Using Transact-SQL Statements
The three Transact-SQL statements that are used to manage security of the Database Engine are CREATE LOGIN, ALTER LOGIN, and DROP LOGIN.
The CREATE LOGIN statement creates a new login. The syntax is as follows:
CREATE LOGIN login_name
{ WITH option_list1 |
FROM {WINDOWS [ WITH option_list2 [,…]]
| CERTIFICATE certname | ASYMMETRIC KEY key_name }}
login_name specifies the name of the login that is being created. As you can see from the syntax of the statement, you can use the WITH clause to specify one or more options for the login or use the FROM clause to define a certificate, asymmetric key, or user account associated with the corresponding login.
option_list1 contains several options. The most important one is the PASSWORD option, which specifies the password of the login (see Example 12.8). (The other possible options are DEFAULT_DATABASE, DEFAULT_LANGUAGE, and CHECK_EXPIRATION.)
As you can see from the syntax of the CREATE LOGIN statement, the FROM clause contains one of the following options:
- WINDOWS Specifies that the login will be mapped to an existing user account (see Example 12.9). This clause can be specified with other suboptions, such as DEFAULT_ DATABASE and DEFAULT LANGUAGE.
- CERTIFICATE Specifies the name of the certificate to be associated with this login.
- ASYMMETRIC KEY Specifies the name of the asymmetric key to be associated with this login. (The certificate and the asymmetric key must already exist in the master )
The following examples show the creation of different login forms. Example 12.8 specifies the login called mary.
Example 12.8
USE sample;
CREATE LOGIN mary WITH PASSWORD = ‘you1know4it9!’;
Example 12.9 creates the login called pete, which will be mapped to a user account with the same name.
Example 12.9
USE sample;
CREATE LOGIN [NTB11901\pete] FROM WINDOWS;
NOTE You have to alter the username and the computer name (in the form domain\username) according to your environment.
The second security statement supported by Transact-SQL is ALTER LOGIN, which changes the properties of a particular login. Using the ALTER LOGIN statement, you can change the current password and its expiration properties, default database, and default language. You can also enable or disable the specified login.
Finally, the DROP LOGIN statement drops an existing login. A login cannot be dropped if it references other objects.
2. The sa Login
System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. The sa login is the login to which was granted all possible permissions for system administration tasks.
NOTE Use the sa login only when there is not another way to log in to the database system.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.