34. Users and security management
Establishing a login account
Management information inventory
Manage database role
Security account authorization
Summary of this chapter
This chapter will learn to manage users in the Microsoft SQL Server 2000 environment and ensure security. Safety management and backup, reply planning, capacity scale and spatial management are the work of DBA routine. Less system security, the information will be lost or destroyed.
This chapter covers the subject related to users and security management. We will learn to establish and manage users login accounts, as well as different verification modes; there will be a SQL Server user ID. The user login is used to verify that the user allows access to the SQL Server, which can be verified by Microsoft Windows NT, Microsoft Windows 2000 or SQL Server. User ID (User ID) assigns user permissions to access specific objects in individual databases. The user ID is related to the user login account, but the two can set the same name, or set a different name, which will be discussed later in this chapter. This chapter learns different types of permissions allocated in SQL Serve R, and how to use permissions. Also learn to simplify user management work using roles. Finally, I will learn the SQL Server 2000 security account authorization. After reading this chapter, you should have common sense to manage users to log in accounts and security.
Establishing and Managing User Login Account Now uses the user to log in account, start managing the first step in managing users and security. This section describes the importance of login accounts, as well as verification modes for maintaining login accounts. Then we will introduce three ways to establish logins: use the SQL Server Enterprise Manager, use Transact-SQL (T-SQL), and use the establishment of login wizards. Last study uses Enterprise Manager and T-SQL to build a new user account. Establishing a user login account Cause The user login account can reach the purpose of protection, avoiding the database that is not intentionally or unintentionally changed by the unrecognized user. Using the user login account to identify each authorized user in SQL Server. Each user login account has a unique name and password. If there is no user login account, all connections to SQL Server will use the same identification symbol so that the secure access range of different levels cannot be set up according to the user of different levels. The user login account can set the secure access of different levels, which is permission to have different access objects and execution functions with different login accounts. We can encrypt some of the data library objects, such as the definition of the pre-depreciation and review to the unfounded user. The user login account can allow the user to insert or update the new information to the database data sheet, and the usual hierarchy can only access the data sheet in the only way reading.
Let's go back to the example of Chapter 18, understand how login accounts limit access. Examples use the viewing table to limit access to specific information. Suppose an Employee tab contains information on the staff name, phone number, office number, level, salary, bonus, etc., in order to avoid confidential information in the user access data sheet, first establish a viewpoint, only contain, such as staff name, phone Number and office numbers and other non-confidential information, through the user login account to limit the access to the base data sheet, only allow access to the checklist. Of course, if you do not use the user to log in to the account to make a control tube, it allows any user to access the inspection table or the information table, and lose the meaning of the use of the viewing table.
Verification mode
There are two types of verification mode: Microsoft Windows authentication and mixing mode verification. In Windows Authentication mode, the job system is responsible for verifying the user, using the authentication of the job system to determine the user's permissions in SQL Server. In mixed mode verification, Windows NT / 2000 and SQL Server are also responsible for verifying users. You must first log in before accessing SQL Server. Therefore, before you select the verification mode, you must decide whether to use a double verification. Let us understand these two verification modes more detailed, then learn to implement these modes. Windows verification
In Windows Verification, SQL Server provides login security through Microsoft Windows NT / 2000. When the user logs in Windows NT / 2000, the user account identity is confirmed. The SQL Server then verifies whether the user is the user confirmed by Windows NT / 2000 and then authenticates the authority of the information inventory. The SQL Server's login program combines Windows secure login programs, and the network security properties are built through the complex encryption process provided by Windows NT / 2000. Since SQL Server and Windows login security verification is combined, this way no longer requires other authentication methods. The only password used to log in is the password of Windows NT / 2000.
Windows verification is better than the hybrid verification mode, because it provides additional security features, including security confirmation, encryption password, auditing, password expiration time, minimum password length, and after the number of failed logins Automatically lock the account.
Mixed mode verification
In mixed mode verification, users can verify with Windows or SQL Server to access SQL Server. When there is a connection from a non-secure system, SQL Server will verify this login and verify that this SQL Server login account is set. This account is verified in SQL Server to check according to the user name and password stored in the database. When trying to connect to SQL Server, the system prompts the user to provide the user name and password. If these information is not provided, the SQL Server system rejects the user's access.
You cannot use Windows authentication mode when you perform Windows 95/98, so you must use SQL Server authentication (with hybrid mode authentication) on these platforms. In addition, the web application requires SQL Server to verify (through Internet Information Server, or called IIS), because users of these applications do not necessarily all in the same domain, so they don't rely on Windows security, then need Use SQL Server security. Other applications that need to access the database also require SQL Server authentication: Some applications are simple and like to use SQL Server security devices because of their application structure. When using the SQL Server security device (in the trust network), the application's developers no longer need to provide the application's own security verification, which simplifies their work.
Set verification mode
Please use the Enterprise Manager to set the verification mode by setting the ENTERPRISE MANAGER:
Enable the Enterprise Manager window, press the right button on the server name in the left pane and select the content from the quick function table and enter the SQL Server property window. Press the security tab as shown in Figure 34-1.
Figure 34-1 "SQL Server Properties" Window's "Security" tab is in this tab, select a security method and start a service account. In the security zone, specify whether you use Windows NT / 2000 and SQL Server (mixed mode) verification, or only use Windows NT / 2000 verification. Here you can specify the login audit level. Log in to the audit hierarchy specifies which login is logged in. You should choose the audit level according to safety, there are four grades available:
No preset options; no login audit. Successfully record all successful login attempts. Failure Records All failed login attempts. All login attempts have all been recorded.
Note The audit level is a database properties, the same, this setting will be used to all logins.
In the Startup Service Account area, specify the Windows NT account used when starting the SQL Server service. You can use a built-in native system account or provide a password after specifying an account. Accept the settings as determined.
Login account and user
The next few chapters will learn to establish login accounts and users. Before you begin, you need to understand what to log in to your account and users. This only brief definition of these two words here.
We can see that in order to connect to the database, we may need a Windows NT / 2000 user account, perhaps at the same time you may need to use SQL Server authentication. Whether using Windows NT / 2000 Verification or Mixed Mode Verification, the account connected to SQL Server is called the "login" account. In addition to the SQL Server login account, each repository assigns a set of specified users, which are virtual accounts. These virtual accounts provide an alias for the SQL Server login account. For example, in the NorthWind database, there may be a user's name as a Manager, and this user name and SQL Server login account guest are related; in the Pubs database, there may be a user name is also Manager, and this user name is SQL Server login account SA is related. In the preset, the SQL Server login account and the database user ID setting are not associated, so there is no permission.
Establish SQL Server login account
Most management tasks can be implemented in a variety of ways, and the establishment of a user login account is no exception. You can create logins through any of three ways: using Enterprise Manager, use T-SQL or use Create SQL Server Login Wizard. In this section, you will learn how to build a SQL Server login account using these three methods.
Establish SQL Server login account with Enterprise Manager
To use Enterprise Manager to create a SQL Server login account, follow these steps:
In the left pane of the Enterprise Manager window, expand the server group, the server, and then expand the security data clip. Press the right button on the login, then select Add to add login in the fast display, enter the SQL Server login properties window, as shown in Figure 34-2. Type SQL Server login account in the Name text block of the general tab. If you are using Windows authentication, then this name must be a valid Windows NT or Windows 2000 account name. Next, specify the domain of Windows NT or Windows 2000 in the domain text block. Specifies the preset repository and language used by the user in the preset area. In the verification area, specify whether you use the Windows NT or Windows 2000 account or use SQL Server to verify. If you select SQL Server authentication, use a mixed mode verification. Figure 34-2 "SQL Server Login Properties" Window's "General" Tag
Press the server role page sign as shown in Figure 34-3. In this tab, you can specify a new login-selected server role, select in the list of roles that can be used. Press Properties to allow viewing and modify the selected role. (The discussion of the role will be made in the
Figure 34-3 "SQL Server Login Properties" Window "Server Role" Table Sign
Select the information inventory tab, as shown in Figure 34-4. This tab can specify the database that the permissions can access. (Database permission will be explained in the section "Management Information Inventory Privileges" section.) You can select multiple databases and roles available for these databases. In view of the property, you can review and manage the database role properties.
Figure 34-4 "SQL Server Login Properties" window "Data Inventory" Types
Once you have completed the specified option, press OK to save the login account. To see a new login account and other login account, you can select the login folder in Enterprise Manager. All login accounts are listed in the order on the right.
Establish a login account using T-SQL
Using T-SQL can establish a login account, you need to use the sp_addlogin pre-store or sp_grantlogin pre-depreciation. SP_ADDLOGIN Preparation can only join users with SQL Server to the SQL Server library. SP_Grantlogin Pre-depicting programs can join users with Windows NT / 2000.
The syntax of the SP_ADDLOGIN pre-store is as follows:
sp_addlogin [@loginame =] 'login'
[, [@passwd =] 'password']
[, [@Defdb =] 'Database']
[, [@Deflanguage =] 'language']
[, [@SID =] 'sID']
[, [@encryptopt =] 'encryptionOption']
The optional parameters are as follows:
The password specifies the SQL Server login account password. The preset value is NULL. The database specifies the preset repository for the login account. The preset value is the Master database. Language Specifies the preset language of the login account. The preset value is set for existing SQL Server language. Safety Identification Code (SID) Specifies a security identification code (a unique value). If this value is not specified, the system will automatically generate a value. Users typically do not use SID parameters, but administrators may use SID in some cases. When DBA performs troubleshooting tasks, you may need to use SID to determine which login account being checked. The SID parameter is the internal identification code of the login account. Encryptopt_Option Specifies whether to encrypt the password in the system data sheet. The preset value is NULL, indicating that the password is encrypted. Specifies Skip_Encryption to indicate that the password is not encrypted. If you specify skip_encryption_old, the encrypted password that is encrypted in earlier versions will no longer be encrypted. This setting is only needed when you want to avoid passwords in the encrypted system data sheet. Here is an example of a simple new login account:
EXEC SP_ADDLOGIN 'PATB'
Remember to use an Exec keyword before the pre-depreciation name. Below is a more complex new login account example.
sp_addlogin 'Sharonr', 'Mypassword', 'Northwind', 'US_ENGLISH'
This command has established a user named Sharonr and uses mypassword password. The preset repository is Northwind, the preset language is U.S. ENGLISH. In general, let SQL Server to create security identification than yourself. The sp_grantlogin pre-depreciation has the following syntax:
sp_grantlogin 'login_name'
Examples of using sp_grantlogin pre-deposited are as follows:
EXEC SP_GRANTLOGIN 'MOUNTAIN_DEW / DICKB'
After adding these login accounts, select the login data clip in the pane on the left to check these login accounts in Enterprise Manger. Use the establishment of login elves to use the following steps to establish a SQL Server login account using the establishment login wok:
Expand a server group and select a server. Select the wizard in the tool function table. Expand the database data clip in the elf dialogue, select the establishment of the login wizard, as shown in Figure 34-5, press OK. At this point, you will be displayed to establish the login elf, as shown in Figure 34-6.
Figure 34-5 Select the wizard picture
Figure 34-6 Welcome to the establishment of login wizard screen
Press Next to select the verification mode screen selected this login, as shown in Figure 34-7. In this picture, you can specify whether you use Windows authentication or SQL Server authentication (mixing mode).
Figure 34-7 Select this login verification mode screen
Press Next to enter the account verification screen using Windows NT or use the verification screen of SQL Server, the screen that appears depends on the verification mode selected in step 2. Figure 34-8 shows a later picture. Specify the login ID and password in this picture. If you select Windows NT authentication, type the domain name and the user account name.
Figure 34-8 Verification screen using SQL Server
Press Next, the access screen agreed to the security role, as shown in Figure 34-9. In this picture, you can select the database role assigned to the login account.
Figure 34-9 Againing access to safety roles
Press the next step to enter the agreed to access the database, as shown in Figure 34-10. You can choose the library that logged into your account can be accessed in this picture. Figure 34-10 Agree to access the database for the database
Press Next to enter the completion of the login wizard screen, as shown in Figure 34-11, here you can check the summary information in the text block. To change the settings, press the previous screen to follow the previous screen, and then complete the establishment of a new login account.
Figure 34-11 Complete the establishment of login wizard screen
Establishing SQL Server User SQL Server users can use Enterprise Manager or T-SQL. (Not used in SQL Server is not used to complete the wizard). SQL Server users will be created in both ways. Keep in mind that SQL Server users are defined for a specific database, and the specific user login name has different permissions for the database. The SQL Server user ID can be considered a synonym of the SQL Server login account, but both when logging in does not need to use the same name.
Note Before establishing a SQL Server user, you must first define the SQL Server login account for the user because the user name needs to be used to log in to the account.
Establishing a user with the Enterprise Manager is different from the SQL Server login account, the login account is built in the Security data folder of Enterprise Manager, and the SQL Server user is established in the specified database folder of the Enterprise Manager left menu. To build a user using Enterprise Manager, follow these steps:
Press the right button on the database that needs to be created, select the new increase in the quick function table, then select the database user, enter the database user properties window, as shown in Figure 34-12. Type a valid SQL Server login account name in the login Name drop-down list and type a new user name in the user name text block. Select the appropriate verification block in the Database Role Use Permissions, assign the database role for the new user. It can be seen later in this chapter that the permissions are allocated to the user for these roles.
Figure 34-12 Database user property screen
Press the property button to enter the database role properties screen, as shown in Figure 34-13. In this window, you can change the selected database role. This task will be explained in the "Management Database Role> section in this chapter.
Figure 34-13 Database role properties screen
After we have completed these settings, you can establish a database user by confirming twice. Establishing a user using T-SQL To create a library user using T-SQL, you can execute sp_adduse pre-deposited programs. This pre-store can be executed in ISQL or OSQL, the syntax is as follows:
sp_adduser [@loginame =] 'login'
[, [@name_in_db =] 'user']
[, [@GRPNAME =] 'group']
Login This parameter is the name of the SQL Server login account, which is the information that must be provided. User variables are new user names, and Group is a group or role of new users. If no User value is specified, its value will be the same as the login parameter. The following command creates a new database user with a named Jackr and Windows NT or Windows 2000 account fort_worth / db_user:
Sp_adduser 'fort_worth / db_user', 'Jackr'Fort_worth is a system or domain name, while DB_USER is a Windows NT or Windows 2000 account name. Managing Information Inventory Acquisition Acquisition (Permissions) is used to control the access of the database, and specify the user operable to operate the specific database operation. Servo and data inventory can be set. Servo Access Permissions allow DBA to perform database management tasks, and data inventory is used to allow or disable access to access library objects and statements. The type of access rights will be discussed in this section, and how to assign the resemble permission. Server Access Permissions Server Access Permissions are assigned to DBA, allowing DBA to perform administrative tasks. These access rights are defined in the fixed server role. The user login account can also be assigned to the fixed server role, but these roles cannot be changed. (The server role will be discussed in this chapter using the fixed server role> section.) Server access rights include shutdown, create database, backup Database, and CheckPoint access permissions. Server access rights can only be authorized to use DBA as execution management tasks without having to change or grant personal users. Database object access rights Database item permissions are permission types, which belong to the type of access to the database. When you use SQL statements (such as SELECT, INSERT, UPDATE, and DELETE) to access item access, you will need to use object access rights. Extracts are also required when performing pre-depreciation using Execute. Use Enterprise Manager to specify item access rights To use Enterprise Manager to grant the Database Parts Access Permissions, follow these steps:
Expand the server group, expand a server, expand the database that needs to be assigned the deposit permissions, and then select the user folder. The user will be listed in the right pane. Please press the right button on the user name and select the content from the quick function table, enter the database user properties window, as shown in Figure 34-14.
Figure 34-14 "Database User Properties" Window
Press the Permissions button to enter the database user properties window, as shown in Figure 34-15. (You can also press the right button on the user name, select all the work from the quick function table, then select Manage Use Permissions to enter this window). Here you can manage access rights to the user, you can assign multiple access rights for the user, specify SELECT, INSERT, UPDATE, DELETE, EXEC, and DECLARATIVE REELETE, EXEC, and Declarative Referenceial Integrity (DRI) form Access permissions. These objects are columns in the material data column. You can also use the options above the screen to list all items, or only the items that this user has permission.
Figure 34-15 Data Library User Properties Window in the Window
Use T-SQL allocated item access rights to perform GRANT statements, which can be specified to the user using T-SQL. The grammatical grammar of GRANT is as follows:
Grant {all | permission}
[Column On {Table | View}] |
[On Table (Column)] |
[On View (Column)] |
[ON {stored_procedure | extended_procedure}]]
TO Security_ACCOUNT
[With grant option]
[As {group | role}] security_account parameters must be one of the following account types: Microsoft SQL Server User Microsoft SQL Server Role Microsoft Windows NT or Windows 2000 User Windows NT or Windows 2000 Group Use Grant Option Keyword Allowable users Grant this permission to other users. This is useful when granted access to other DBAs. However, you should carefully use the GRANT option. The AS option specifies the permissions to perform the GRANT statement. The GRANT statement must be executed when the user or role is granted permissions. The GRANT statement can be used in the following manner:
Grant SELECT, INSERT, UPDATE
On Customers
To Guest
With Grant Option
As Accounting
Because the Accounting role can grant access permissions on the Customers sheet, you want to use the as an accounting option. Grant Option Keywords Let MaryW grants access rights to other users.
Related Information To view a list of access permissions specified in the GRANT statement, see the GRANT in the "Online Series> Index.
Use T-SQL to undo item access permissions You can use the T-SQL Revoke command to undo the user's object access rights. Revoke statement grammar is as follows:
Revoke [Grant Option for]
{All [privileges] | permission}
[Column On {Table | View}] |
[On Table (Column)] |
[On View (Column)] |
[ON {stored_procedure | extended_procedure}]]
{To | from} security_account
[Cascade]
[As {group | role}]
Security_account parameters must be one of the following account types: Microsoft SQL Server User Microsoft SQL Server Role Microsoft Windows NT or Windows 2000 users Windows NT or Windows 2000 groups are the same as withdrawal access, Allow you to use it before Grant Option Grants Grant Access Permissions. The AS option specifies the permissions to perform the Revoke statement. The following is an example of using the Revoke state:
Revoke all
On Customers
TO MARYW
Revoke Al L states will remove all access rights owned by the user Maryw in the Customers Data Sheet.
Related Information To view the list of access permissions specified in the Revoke statement, please review the Revoke in the "Online Series" index.
Database statement access rights In addition to data library access, you can also assign state-of-style access rights. The object access rights allow the user to access existing objects in the database, while the statement access rights authorizes the user to establish a database item, including the database and data sheet. The statement access rights are as follows: Backup Database Commands BACKUP DATABASE Commands Backup Log Execute Backup Log Commands CREATE DATABASE to create a new repository Create default Used to create a preset value of the associated to field CREATE Procedure to establish a pre-depicting program CREATE Rule is used to create rules create table to create new information table CREATE VIEW to create new review statements Access permissions can be assigned with Enterprise Manager or T-SQL. Using Enterprise Manager assigning statement Access permissions To use Enterprise Manager to grant the repository to the user, follow these steps: Expand a server group, expand a server, then expand the database data clip . Press the right button on the database that needs to be assigned free, and selects the content from the quick function table, display the properties window of the database, as shown in Figure 34-16.
Figure 34-16 Property Window in the Database
Select the permissions page sign as shown in Figure 34-17. Here you can assign a replacement limit for the user who access the database. These data columns contain the verb block, which specifies the statement access rights to be assigned, and the user / role field lists the user and roles of this database.
Figure 34-17 Database properties window permissions page
Using T-SQL allocation statement access rights You can use the Grant T-SQL statement to assign state-ortened access rights to users. The grammatical grammar of GRANT is as follows:
Grant {all | statement}
TO Security_ACCOUNT
The statement access permissions that can be assigned to users include Create Database, Create default, Create Procedure, Cteate Rule, Create Table, Create View, Backup Database, and Backup log, as mentioned earlier. For example, to add Create Database and Create Table to the user account ELEA, use the following command:
Grant Create Database, CREATE TABLE
To 'ELEA'
So, adding new statement access rights for the user account is not difficult. Using T-SQL to undo statement access rights You can also use T-SQL state Revoke to undo the statement access rights of the user account. Revoke statement grammar is as follows:
Revoke {all | statement}
From security_account
For example, to undo from the user account ELEA, you can use the following command:
Revoke Create Database
From 'Elea'
As seen, the declarative access to the user is not a complex process. Managing Database Roles Use the database role to simplify the work of managing users access rights. The database role is designed to allow a group of users to accept the same data inventory, so there is no need to assign these access rights. Do not assign access permissions for each user, you can create a role for a group of users and then assign a renovation. The role is designed for special working groups, working categories, or work tasks. In this case, according to the work performed by the new user, they can be a member of one or more database roles. For example, the role may define a working category such as a payment account, an accepted account, an engineering, and human resources. When a user joins these departments or groups, he or she is assigned to a member of this role. A user can be a member of one or more roles, but does not necessarily require the user to become a member of a role. In addition to being a member of the database role, the user can also assign personal access rights. To establish and modify most of the tasks performed by the role and DBA, establish and modify the database role can also be done through the Enterprise Manager or T-SQL command. (SQL Server does not provide this elves.) These two methods must complete the same routine task: establish a database role. Distribute the right to the role. Assign the user as a role. When viewing the role, you can see access rights assigned to the role and the user assigned to the role. Using the Enterprise Manager management role To use Enterprise Manager to create a database role, follow these steps: Expand a server group to expand a server, then expand the database data clip. Press the right button on the database that needs to be established (this example uses northwind) and selects a new increase from the quick function table, then select the database role. Another method can expand the database, press the right button on the role and select the new repository role from the quick function table. Both methods can enter the database role properties window, as shown in Figure 34-18.
Figure 34-18 "Database Role Attribute" Window
Type the name in Name text block to assign a descriptive name for the role - please select a name that helps to remember the role function. Press to increase the role allocation user. At this time, you will display the list of users who can access the database, as shown in Figure 34-19. Select the user to be assigned to the role. If you want to deselect the user, just press it on the user name. After completing the modified role member, press OK to establish a new role. Return to the Enterprise Manager window.
Figure 34-19 New role member dialogue
To assign some of the rights to the role, first expand the role data clip to turn on the database role property window, press the right button on the role name and select the content from the quick function table. Then press the permissions, enter the database role property - Northwind window, as shown in Figure 34-20. In this window, you can assign different access rights to the object roles in the database. Select the appropriate verification box in the list. The database object is listed in the material data column. Selecting the option above the window to view all items, or list the objects with this role. Once a role is assigned to a user, the user gets all access rights assigned to this role. After the role is established, you can modify in the database role properties window. To modify the role, follow the steps to increase access permissions for the role. You can also add new and delete users and access permissions in the database role properties window. Use the T-SQL management role or you can use the sp_addrole pre-depreciation to establish roles. The syntax of the SP_ADDROLE pre-deposit program is as follows:
sp_addrole [@roleName =] 'role' [, [@Ownername =] 'Owner']
Figure 34-20 Database role properties - Northwind window
For example, to join a role named Readonly in the Northwind database, use the following T-SQL commands:
Use northwind
Go
sp_addrole 'Readonly', 'DBO'
Go
Use northwind commands Select Northwind as the current database. If there is no specified database, the role is established in the preset repository. This pre-depression program can only establish roles. To increase access to the role, use the GRANT statement described above. To delete access permissions for roles, use the revoke statement. For example, to add SELECT access permissions for Employees, Customers, and Orders table for Readonly roles, use the following GRANT statement:
Use northwind
Go
Grant SELECT
On Employees
To Readonly
Go
Grant SELECT
On Customers
To Readonly
Go
Grant SELECT
ON Orders
To Readonly
Go
To add users to the role, use the sp_addroleMember to prepare the program. The syntax of the sp_addrolemember pre-store is as follows:
sp_addroleMember 'Role', 'Security_account'
The following command can add users to the Readonly role:
Use northwind
Go
sp_addroleMember 'Readonly', 'Guest'
Go
Using a fixed server role in SQL Server installation, a large number of pre-defined servo roles are established. These fixed servers roles are used to grant access rights to DBA, which contains server access rights and objects and statement access rights. The list is as follows: BulkAdmin can perform a large number of insertion of DBCREATOR can establish and change the database DiskAdmin Administrative Disk Archives ProcessAdmin can manage S QL Server Programs SecurityAdmin can manage login accounts and build information inventory permissions ServerAdmin can set any server options, and you can turn off the database setupadmin to manage linkage, and start programs, sysadmin can perform any servo. The device behavior assigns a fixed server role for the user account to allow the user to perform the management task of the role has access rights. It may be better if you need to use the same management account as you need. Compared to the database role, the fixed server role is more easily maintained than individual access rights, but the fixed server role cannot be modified. You can allocate a fixed server role for a user in follows:
Expand a server group in Enterprise Manager, expand a server, expand the security data clip, and select the server role. Press the mouse right on the fixed server role that needs to be added to the user, and select the content from the quick function table, enter the servo role window, as shown in Figures 34-21.
Figure 34-21 Server Role Window
Press new, assign fixed server roles for new user accounts. This will call the new member dialogue, as shown in Figure 34-22.
Figure 34-22 New member dialogue block
After selecting the user who needs to add a fixed server role, press OK to return the server role property window. Press to determine the use of a safe role. Security Account Authorization SQL Server 2000 creates security using Kerberos security mode. (About Kerberos Security Mode can be referred to Chapter 2.) SQL Server 2000 uses the Kerberos communication agreement to support mutual authentication between the client and the server. This allows the user-end security identification to flow between the computer, so that it can be connected to different servers; when logging in to a new server, you can use personalized user to identify access. Access. This is the safety account authorization. Let's take an example of a security account authorization. Assume that one user is connected to Server A to NTDOMAIN / ALEXR, while Servera is connected to the ServerB. Then SERVERB knows the security identification of the connection as NTDOMAIN / ALEXR. This reduces the need for users to log in to Serverb. If you want to use a security account authorization, all servers connected must be able to perform Windows 2000 and support Kerberos, and you must be using the Active Directory service. The following options must be set in the Active Directory to authorize: Account sensitive unable to authorize the authorized users absolutely cannot choose this option. Accounts can authorize SQL Server 2000 Server Accounts must be selected. Computers can authorize the server that executes SQL Server 2000 must select this option. Setting SQL Server Before using security account authorization, you must first set SQL Server 2000 to accept licenses. Authorization contributes to each other to confirm each other. To use a security account authorization, SQL Server 2000 must have a Service Principal Name (SPN) that Windows 2000 account domain administrator assigned. The SPN must be assigned to the Server's server account on your computer. The SPN must prove that SQL Server is certified by a specific server and Windows 2000 account domain administrator. You can make your domain administrator to create a SPN for SQL Server using the SetSPN public program acquired on Windows 2000 Resource Kit. To establish a SPN, do the following command: setspn -a mssqlsvc / host: port serviceAccount
The following is an example of using this command:
Setspn -a mssqlsvc / myserver.mydomain.mycompany.com SQLACCOUNT
Related Information Related information about SETSPN utilities, see the Windows 2000 file.
You must also use TCP / IP to use security account authorization, you cannot use a named pipe because SPNs use specific TCP / IP communication terminals. If you use multiple connections, each connection must have a SPN. You can authorize it with the Localsystem account. SQL Server will register your registration when the server starts, and automatically register SPN. This option is easy to authorize the user account using the domain. However, when SQL Server is turned off, SPN will not be registered for the localSystem account. In order to be authorized under the Localsystem account, do the following commands in the SetSPN public program:
SetSPN -A MSSQLSVC / Host: Port ServiceAccount
Note If you want to change the server account in SQL Server 2000, you must delete the previously defined SPN and create a new account.
