SQL Server 2008 Fixed Server 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. 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. 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.

Source of Information : Apress Beginning SQL Server 2008 for Developers From Novice to Professional


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner