The security of the Database Engine can be set up using
- SQL Server Management Studio
- T-SQL statements
The following subsections discuss these two alternatives.
Managing Security Using Management Studio
To create a new login using SQL Server Management Studio, expand the server, expand Security, right-click Logins, and click New Login. The Login dialog box (see Figure 12-2) 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.
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 SQL Server 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 Windows 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.3). (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 Windows user account (see Example 12.4). 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 database.)
The following examples show the creation of different login forms. Example 12.3 specifies the login called mary, with the password you1know4it9!
USE sample;
CREATE LOGIN mary WITH PASSWORD = 'you1know4it9!';Code language: PHP (php)Example 12.4 creates the login called pete, which will be mapped to a Windows user account with the same name.
USE sample;
CREATE LOGIN [NTB11901\pete] FROM WINDOWS;Code language: PHP (php)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, credentials, 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 (owns) other objects.
