70-432 : Fixed server and database roles

For the 70-432 exam you must understand the difference between the server and the database within the server.

You will need to under the fixed server-level and fixed database-level plus the associated stored procedures.

I found the following very useful summary:

Fixed server roles: These are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed server role	Description
sysadmin	Can perform any activity in SQL Server
serveradmin	Can set server-wide configuration options, shut down the server
setupadmin	Can manage linked servers and startup procedures
securityadmin	Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords
processadmin	Can manage processes running in SQL Server
dbcreator	Can create, alter, and drop databases
diskadmin	Can manage disk files
bulkadmin	Can execute BULK INSERT statements

Here is a list of stored procedures that are helpful in managing fixed server roles:

sp_addsrvrolemember	Adds a login as a member of a fixed server role
sp_dropsrvrolemember	Removes an SQL Server login, Windows user or group from a fixed server role
sp_helpsrvrole	Returns a list of the fixed server roles
sp_helpsrvrolemember	Returns information about the members of fixed server roles
sp_srvrolepermission	Returns the permissions applied to a fixed server role

Fixed database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed database role	Description
db_owner	Has all permissions in the database
db_accessadmin	Can add or remove user IDs
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements
db_datareader	Can select all data from any user table in the database
db_datawriter	Can modify any data in any user table in the database
db_denydatareader	Cannot select any data from any user table in the database
db_denydatawriter	Cannot modify any data in any user table in the database

Here is a list of stored procedures that are helpful in managing fixed database roles:

sp_addrole	Creates a new database role in the current database
sp_addrolemember	Adds a user to an existing database role in the current database
sp_dbfixedrolepermission	Displays permissions for each fixed database role
sp_droprole	Removes a database role from the current database
sp_helpdbfixedrole	Returns a list of fixed database roles
sp_helprole	Returns information about the roles in the current database
sp_helprolemember	Returns information about the members of a role in the current database
sp_droprolemember	Removes users from the specified role in the current database

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Another blog which also covers the above is http://articles.techrepublic.com.com/5100-10878_11-1061781.html. This article had the advantage of several screenshots show how to administer security via SQL Server Management Studio (SSMS) but I just don't like an example where you allocate db_accessadmin to guest - this is just to counter intuitive!