Presents your 
SQL SERVER E-NEWSLETTER for May 1, 2003

<------------------------------------------->

EXAMINE THE POWER OF FIXED SERVER ROLES FOR DATABASE ADMINISTRATION

Fixed server roles can be a DBA's best friend when it comes to modifying
and administering a SQL 2000 Server or, better yet, offloading some
administrative duties to other database team members. Because fixed server
roles are defined at the server level, they have permission to perform
specific server-level administrative tasks, such as killing runaway queries
or granting, denying, and revoking privileges.

Each fixed server role has explicit permissions associated with it, so
it's important to add team logins to the right role. These roles have very
powerful permissions, and adding logins should not be done without some
serious consideration and prior planning.

Be very leery of adding someone to a fixed server role when a fixed
database or other group role would suffice. A good rule of thumb is to find
the correct role that gives the user just the right amount of permission--and
nothing more.

The fixed server roles include:

*sysadmin--This is the superman of fixed server roles and can perform
any activity in SQL Server. This role cannot be stopped from accessing
any database. Consider adding someone to this role very carefully.

*serveradmin--This role sets up server-wide configuration options and
can shut down the server. In addition, it can run the sp_fulltext_service
stored procedure to make changes to full text-search capability.

*setupadmin--This role typically manages linked servers and startup procedures.

*securityadmin--This role manages logins and CREATE DATABASE
permissions, read error logs, and password changes. Help desks or others
who set up new accounts typically are members of this role.

*processadmin--This role manages processes and can run the KILL 
command to end a SQL process.

*dbcreator--This role can create, alter, and drop a database.

*diskadmin--This role manages disk files and is really more of a
throwback to SQL Server 6.5.

*bulkadmin--This role executes a BULK INSERT statement.

There are a few very useful stored procedures that will help you when
working with fixed server roles. If you can't remember all of the roles you
have to choose from, sp_helpsrvrole will provide you with the above
list. Sp_svrrolepermission will give you a list of permissions for a 
specific role.

In the example below, the stored procedure will return the two
permissions granted to the bulkadmin role: the permission to add 
members to bulkadmin and the permission to do a BULK INSERT.

Exec sp_srvrolepermission 'bulkadmin'

The stored procedure returns the following results:

ServerRole                       Permission
----------------- ---------------------------------
bulkadmin                 Add member to bulkadmin
bulkadmin                      BULK INSERT

(2 row(s) affected)

----------------------------------------

CORRECTION

The May 1, 2003, SQL Server TechMail, "Examine the power of fixed 
server roles for database administration," contained a typographical error.
Sp_srvrolepermission was incorrectly spelled.

We apologize for any inconvenience this may have caused.

----------------------------------------