Microsoft SQL Server 2008 R2 Unleashed- P36:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 304 CHAPTER 11 Security and User Administration NOTE Keep in mind that when a login is assigned to certain fixed server roles they have implied permissions that cascade to the database level. For example if a login is assigned to the sysadmin role that login can perform any activity on the server and it can also perform any action on any database on that server. Similarly if a login is added to the securityadmin role the login can change permissions at the database level as well as the server level. All the fixed server roles are listed in the SQL Server Management Studio SSMS Object Explorer. Figure shows the Object Explorer with the Server Roles node expanded. You can right-click any of the roles and select Properties to display the logins that are currently members of the role. FIGURE Fixed server roles in Object Explorer. Fixed Database Roles SQL Server provides fixed roles that define a common set of permissions at the database level. These fixed database roles are assigned to database users. The permissions defined for the fixed database roles cannot be changed. Table shows the fixed database roles and their permissions. Download from Managing Principals 305 TABLE Fixed Database Roles Role Permission db_accessadmin Allowed to add or remove database access for logins. db_backupoperator Allowed to back up the database. db_datareader Allowed to read all user table data. db_datawriter Allowed to change the data in all user tables. db_ddladmin Allowed to run any Data Definition Language DDL command against the database. This includes commands to create alter and drop database objects. db_denydatareader Denied the right to read all user table data. db_denydatawriter Denied the right to change the data in any of the user tables. db_owner Allowed to perform any action on the database. Members of the sysadmin fixed server role are mapped to this database role. db_securityadmin Allowed to manage permissions for database users .
