Authenticating Logins - Server Roles
(Page 21 of 24 )
The server roles are a fixed set of identities that a user can assume. They represent server administrator tasks and thus are rather narrow in their scope in order to give you more granular control over what the administrators can do. This is in contrast to earlier versions that offered only either system administrator privileges or user privileges. The different server roles are summarized in Table 2-5.
| Fixed Server Role | Description |
| sysadmin | Performs any activity in SQL Server. |
| serveradmin | Configures serverwide configuration options; shuts down the server. |
| setupadmin | Manages linked servers and startup procedures. |
| securityadmin | Manages serverwide security settings, including linked servers, and CREATE DATABASE permissions. Resets passwords for SQL Server authentication logins. |
| processadmin | Terminates processes running in SQL Server. |
| dbcreator | Creates, alters, drops, and restores any database. |
| diskadmin | Manages disk files. |
| bulkadmin | Allows a non-sysadmin user to run the bulkadmin statement. |
Table 2-5. Server Roles
NOTE Unless mentioned otherwise, all the roles in the following sections grant their members the right to add another member to the role. I'm not really sure why Microsoft did this, but it did and you need to be aware of it.
Primary Server Roles In this section, I refer to sysadmin, serveradmin, securityadmin, and dbcreator as primary server roles, because they encompass the operations that most administrators will perform. (Note, however, this term is not used by Microsoft.) In general, serveradmin, securityadmin, and dbcreator represent the subcategories of the system administrator’s overall duties. A judicious use of these primary server roles will allow the senior administrator to parcel out jobs according to skill level and expertise.
sysadmin
This is the role from which the sa account gets all its abilities. In previous versions of SQL Server, sa was a special account for which security checking was bypassed. In version 7.0, the sa account became a member of the sysadmin server role. The sa account is unique in that it cannot be removed from the sysadmin role.
NOTE The SQL Server sa account cannot be renamed. This means you must protect this account with a strong password, because every hacker in the world will know at least one account name on your system.
Actually, the sa account can be renamed by hacking the system table, but I do not recommend this course of action!
The sysadmin role can bestow the same privileges on its other members as it does on the sa account. SQL Server accounts, Windows NT accounts, and Windows NT groups can all be members of this role. In fact, by default the local Windows NT Administrators group for the server is granted membership in the sysadmin role during setup. If your server is also a member of a domain, it is very likely that the Domain Admins global group is a member of the Administrators local group, which means that all domain administrators will have sysadmin privileges on the server.
serveradmin
This server role confers upon its members the ability to set both server configuration options and table options. Although SQL Server 7.0 automatically configures most of the main settings, there will be times when you need to change some of them. For example, to optimize SQL Server’s performance when other BackOffice applications are on the same server, you may need to change the minimum and maximum amount of memory SQL Server uses.
Essentially, members of serveradmin have permission to run the sp_configure stored procedure. Some settings only take effect after you stop and restart the MSSQLSERVER service; others can take effect after you run the RECONFIGURE statement. This statement takes no parameters and simply activates the changes you made with sp_configure.
NOTE Interestingly, members of serveradmin cannot shut down SQL Server using the SHUTDOWN statement. Only members of the sysadmin role can execute SHUTDOWN .As an alternative, members of the Administrators local Windows group and the Server Operators local group can stop and start the MSSQLSERVER service from the Services applet in Control Panel or using the Net Stop command at the command prompt.
The serveradmin role has several other miscellaneous permissions besides being able to run sp_configure. It can drop extended stored procedures, although it cannot create them; it can run the sp_tableoption stored procedure in all databases; and it can run the DBCC printable command.
securityadmin
The securityadmin role is perhaps the second most important server role, because its members may run all security-related system stored procedures, including sp_addlogin and sp_grantlogin, as well as GRANT, DENY, or REVOKE permissions to create databases. It is also the only role besides sysadmin that may read the SQL Server error log, although anyone with access to the file system or xp_cmdshell can read this.
The securityadmin role limits who can create a database by controlling the permission to run the CREATE DATABASE statement. By granting this statement permission, securityadmin effectively controls who can create databases on the server. Notice, however, that the permission does not extend to the DROP DATABASE statement. Only the database owner and members of the sysadmin role may drop a database.
dbcreator
This role may seem as though it duplicates one of the permissions of the securityadmin role, but it really has a different purpose. This role is designed for users who will be managing their own databases because it allows them to run not only the CREATE DATABASE statement but also the ALTER DATABASE statement. It also allows them to rename their databases using the sp_renamedb stored procedure.
The primary difference between the dbcreator and securityadmin roles is that dbcreator is the only role other than sysadmin that has permission to run the ALTER DATABASE command. Members of the securityadmin role can grant users permission to create a database, but the user cannot alter the database’s size, change the location of its files, or add files or file groups. Members of the dbcreator role can make those changes as well as changes to the automatic filegrowth setting.
NOTE Membership in this role grants both ALTER DATABASE and DROP DATABASE permissions in SQL Server 7.0/2000.
 This is from SQL Server Security Distilled, second edition, by Morris Lewis (Apress, ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now. |
Next: Secondary Server Roles >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
