Use a Fixed Server Role

Sử dụng một máy chủ cố định vai trò quản trị một công cụ cơ sở dữ liệu máy chủ như SQL Server có thể là một quá trình tốn nhiều thời gian. nhiệm vụ hành chính bao gồm sao lưu dữ liệu, thêm người dùng mới, sửa đổi các bảng và quan điểm
Nội dung trích xuất từ tài liệu:
Use a Fixed Server Role 11.7 Use a Fixed Server RoleManaging a server database engine such as SQL Server can be a time-consuming process.Administrative tasks include backing up the data, adding new users, modifying tables andviews, and so on. Most SQL Server administrators find it useful from time to time topermit other people to perform these tasks. Assigning other people tasks such as backingup the databases or administering security allows the system administrators to devotemore time to other responsibilities.As a system administrator, I am overwhelmed by the administrative tasks that arerequired to keep several databases operating efficiently. Theres too much to do betweendatabase administration, adding new users, performing backups, and other tasks. Id liketo be able to allow an assistant to assume some of these responsibilities so that I wonthave to be personally involved in performing these tasks.However, Im not looking forward to assigning various permissions to all of myadministrative assistants. For instance, some people are to be designated as securityadministrators whereas others will be responsible for updating table designs.TechniqueSQL Server supports the notion of fixed server roles that define certain administrativeprofiles. Each fixed server role is accompanied by the appropriate permissions to performthe administrative tasks that are associated with the role.Enterprise Manager provides all the dialog boxes that are necessary to assign useraccounts to the fixed server roles that SQL Server recognizes.It is important to note that each fixed server role is global within SQL Server. Thismeans, for instance, that the dbcreator fixed server role is able not only to create newdatabases, but also to make changes to existing databases in SQL Server.SQL Server recognizes eight fixed server roles: • sysadmin. This is the most powerful fixed server role. Members of this role are able to perform all the tasks included with the other roles. • serveradmin. The serveradmin role adjusts the serverwide settings in SQL Server, such as memory usage, authentication mode, and home directories. • setupadmin. This role administers linked servers. A SQL Server installation is able to share SQL Server databases that are located on other computers. The setupadmin group is responsible for creating links to SQL Server installations on other computers. • securityadmin. Members of the securityadmin role add new user and group logins, assign passwords, and perform other security-oriented tasks. • processadmin. This role manages processes that are spawned by SQL Server. • dbcreator. Members of this role are responsible for creating and altering databases. • diskadmin. The diskadmin role adjusts the disk space that is available for databases, sets the database growth increment (as a percent or in megabytes), and specifies the parameters for the SQL Server log file. • bulkadmin. SQL Server 2000 includes a number of statements intended to perform bulk inserts to data. Because the BULK INSERT statement can involve considerable amounts of processing time, SQL Server does not allow anyone other than members of the sysadmin and bulkadmin roles to perform this statement.StepsOften, youll want individual users or groups of users to have database administrativeresponsibilities. For instance, you might want the accounting group to manage its ownlogin names and passwords.In this case, youll want to join the users in the accounting group to certain fixed serverroles, which are predefined special security groups. Each fixed server role defines acategory of administrative tasks, and each member of a fixed server role is able toperform those administrative tasks.SQL Server recognizes members of fixed server roles as people who are authorized toperform these administrative tasks. Each role is accompanied by the appropriate SQLServer permissions that are necessary to perform those tasks. 1. Open Enterprise Manager and expand the Security icon. 2. Click on the Server Roles icon to show the eight fixed server roles in the right pane (see Figure 11.12). Figure 11.12. The Server Roles icon reveals the eight SQL Server fixed server roles.3. Right-click on any of the fixed server role entries and select Properties from the pop-up menu. Alternatively, select one of the fixed server roles and use the Properties command under the Action menu to open the Server Role Properties dialog box, as shown in Figure 11.13. Figure 11.13. The Server Role Properties dialog box shows you the logins that are added to the selected role.4. Use the Add button to open the Add Members dialog box (see Figure 11.14). The Add Members dialog box shows only those logins that have not already been added to the role. In Figure 11.14, only TonyS, the Marketing group, and members of the BUILTIN/Administrators group are not members of the securityadmin fixed server role. Figure 11.14. The Add Members dialog box shows you only those logins that have not been added to the selected role. 5. Select the login to add to the selected fixed server role and click the OK button.CommentsMembership in a fixed server role does not grant access to a database or data within thedatabases. Fixed server roles are intended for administrators and assistant administratorsand do not automatically grant access to any of the data that SQL Server manages.Database object permissions (discussed later in this chapter in How-To 11.10) arerequired to gain access to data and database objects.The special sysadmin role should be reserved for trusted and trained sy ...