Roles in Font

Drawing PDF417 in Font Roles

Three different types of roles exist within SQL Server: fixed server roles, database roles (which refers to the general roles included during installation of SQL Server; component-specific roles such as those for Reporting Services that are added when the component is installed; and user-defined roles), and application roles.

Within SQL Server, specific predefined roles are set up to allow certain tasks and to restrict other tasks. Someone with the right permissions, such as a system administrator, can assign these roles to any user ID or group of user IDs within SQL Server. If you look at the Server Roles node in the Object Explorer, you will see a list of roles as shown in Figure 4-12. But what do they mean You get a little hint if you move to the Server Roles node within SQL Server Management Studio.

These roles, available for anyone to use across the server, can perform the following tasks: bulkadmin: Run BULK INSERT statements. dbcreator: Create, alter, or drop databases as well as restore them. diskadmin: Administer disk files. processadmin: Kill a session running T-SQL code. public: View any database permission but not alter any. securityadmin: Manage logins including passwords for SQL logins and login permissions. serveradmin: Administer the server and carry out tasks such as changing options and even starting and shutting down the server. setupadmin: Work with more than one server, where the servers are linked and manage the linked server definitions. sysadmin: Perform any activity. Server roles are static objects. They contain groups of actions that operate at the server level rather than at the database level. When creating a new login, you could assign these server roles to it if you wanted the login to carry out server actions as well as any database-related actions, if required. If your Windows account belongs to the BUILTIN/Administrators group, then it automatically belongs to the sysadmin server role. You can check this yourself by highlighting the sysadmin server role, right-clicking it, and selecting Properties to bring up the dialog box shown in Figure 4-13. You should see BUILTIN/Administrators listed. As more logins are created, they can be added to this role via the Add button. Although we are not going to alter this for our example database, having Windows XP administrators automatically being administrators for SQL Server can be a bit of a security issue. Many companies batten down their computers so that no user is an administrator of his or her local machine. By doing this, they stop people from adding their own software, shareware, games, or whatever to a machine that is administrated and looked after by a support team. This helps keep the machine stable, and throughout your organization everyone will know that a piece of software developed on one machine will work on any other. Therefore, users won t have administrator rights on their Windows machine and won t have those rights in SQL Server. This is not the case in all organizations. By leaving the Administrators group in the sysadmin role, everyone who has administrator rights on their PC will have system administrator rights within SQL Server. As the owner of the database, you have now lost control of the security and development of your SQL Server database.

Because this book assumes that we re using either a standalone PC or a secure set of users, it is safe to keep the Administrators group. However, you will find that this group is usually removed from database setups to keep the security of the database intact. It is worth keeping in mind that before removing the login, or removing it from the sysadmin role, you should set up a new group or user as a system administrator to prevent locking yourself out.