A Security Roadmap - Server Roles in 7.0 and 2000 (Page 5 of 7 ) Version 7.0 of SQL Server is the first that treats permissions as something to be granted based on someone’s role instead of on discrete rights for individual users. SQL Server 7.0 and 2000 have built-in server roles that exemplify this change because some permissions cannot be granted to individuals at all. In order to gain the authorizations listed, the user must be a member of one of the built-in roles. Chapter 2 covers the details of which permissions go with each role, so here you’ll just have a quick look at each role to get an idea of where it will fit into your security picture: - sysadmin: This is the system administrator role, as you would expect. The primary distinction between versions 6.5 and 7.0 is that the sa account gets its permissions by virtue of being a member of this role rather than being a specially recognized account, as it was in version 6.5. The benefit of this change is that now users can use either a Windows NT account or group or a SQL Server login to gain the privileges of the sa account instead of sharing an account and password.
- serveradmin: This role is for administrators who will be managing SQL Server itself, but not databases or objects in it. Membership in this role gives the user permission to perform tasks such as changing memory settings, shutting down the server, and setting options on tables that affect the server (for example, DBCC PINTABLE). It does not, however, grant permission to view or modify data in any database, so this role is perfect for an administrator who should not have complete control over sensitive data.
- setupadmin: This is a special-purpose role for administrators who need to configure settings for remote servers or run a stored procedure at startup. It has limited capabilities and is normally used in combination with other roles, such as serveradmin or processadmin, to weave the permissions together for an administrator who should have fewer rights than those granted to sysadmin.
- securityadmin: This is self-explanatory. This role has permissions to manage access to the server and to databases. Permissions include managing logins, setting up login information for linked servers, and granting access to databases. Though this role does not innately have rights to any database, members of this role can grant themselves access to the database, but they cannot grant permissions to objects in the database through membership in this role. Also, in case you were worried, members of securityadmin cannot assign themselves to the sysadmin role.
- processadmin: This has one permission: executing the KILL command. The only use I can think of for this role is for technical support personnel or assistant administrators who need to halt processes on a regular basis. In anormal environment, killing a process should be a rare event, so this role should get little use.
- dbcreator: This role does just what the name implies: it allows its members to create databases and alter them. Remember that the user who creates a database is automatically mapped to the dbo database user account and is the first member of the db_owner database role (these are discussed in detail toward the end of the chapter). Further, members in this role will need to understand the basics of how SQL Server stores data so that they can make good decisions about where to put files, how to manage file growth, and when to use file groups. This role is well designed for development staff who need full control over a database during application development.
- diskadmin: This role is a relic from the days of SQL Server 6.5. Its permissions allow members to manage disk devices, which SQL Server 7.0 does not use. Because the DISK INIT command no longer has any purpose in SQL Server 7.0, this role is really only useful for working with devices created in SQL Server 6.5; otherwise, it is not useful.
- bulkadmin: This role is new in SQL Server 2000, and its sole purpose is to grant permission to execute the BULK INSERT command. This can be useful in scenarios such as data warehousing, in which data needs to be inserted into tables in large quantities. Because a scheduled script or a Data Transformation Services (DTS) package usually handles BULK INSERT operations, a likely choice for membership in this role is the account used by the service executing the script.
From these short descriptions, you can probably draw the conclusion that the sysadmin, securityadmin, and dbcreator roles will be the most useful. In terms of how you can use them to build a security plan, it makes sense to limit the scope of what an administrator can do to just the permissions she needs. Unlike version 6.5, SQL Server 7.0 and 2000’s server roles permit the creation of different levels of administrator privileges: - You can reserve the sysadmin role, with its complete control over the server, for senior-level, experienced administrators.
- You can combine securityadmin, setupadmin, processadmin, and serveradmin to create a set of permissions for more junior administrators.
- You can grant control to databases on an individual basis through the dbcreator role.
 This chapter is from SQL Server Security Distilled, by Morris Lewis (Apress, 2004, ISBN: 1590592190). Check it out at your favorite bookstore today. Buy this book now.
|
Next: Database User Accounts >>
More Windows Security Articles
More By Apress Publishing |
/ 2 
