The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P30: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | Managing Security Chapter 4 127 and have the required permissions associated with them. Although you are limited to the built-in server-level roles you can create new database-level roles in addition to those available to suit more specific needs. SQL Server logins Windows accounts and Windows groups can be added to server-level roles. Server-level roles are as follows sysadmin - Perform any activity in the server. By default the BUILTIN Administrators group and the local administrator s group are members of the sysadmin role. serveradmin - Change server-wide configuration options and shut down the server. securityadmin - Manage logins and their properties. They will be able to reset passwords for SQL Server logins and GRANT DENY and Revoke database-level and server-level permissions. processadmin - End processes running in an instance of SQL Server. setupadmin - Add and remove linked servers. bulkadmin - Run the BULK INSERT statement. diskadmin - Manage disk files. dbcreator - CREATE ALTER DROP and restore any database. You can find the following objects in the master database that can help when working with server-level roles sp_helpsrvrole - Returns a list of server-level roles. sp_helpsrvrolemember - Returns information about the members of a server-level role. sp_srvrolepermission - Displays the permissions of a server-level role. IS_SRVROLEMEMBER - Indicates whether a SQL Server login is a member of the specified server-level role. - Returns one row for each member of each server-level role. sp_addsrvrole_member - Adds a login as a member of a server-level role. sp_dropsrvrole_member - Removes a SQL Server login or a Windows user or group from a server-level role. 128 Chapter 4 Managing Security There are two types of database-level roles fixed database roles that are predefined in the database and flexible database roles that you can create. The fixed database-level roles are db_owner - Can drop the database as well as permission to .
