Replacing Database Groups

Database groups were used in SQL Server 6.5 for simplified management of large number of database users. Like for granting and withdrawing permission to many users simultaneously, database groups could be used. However SQL Server 7.0 does not support database groups. This has been replaced by SQL Server database roles which is an improvement over SQL Server database groups. A user apart from being a member of the public group can be a member of only one group in SQL Server 6.5 whereas in case of SQL Server 7.0, there can be many roles for every user and there are combined result users permissions for all roles an individual user is member of.

Database Role Types

Database roles are of three types- Fixed, Public and User-defined. At the database level are defined the fixed database roles which lie in each database. Fixed database roles cannot be added, deleted or modified. Only users can be added as a member of a fixed database role.

Fixed Database Roles

Fixed database roles are of nine types- db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader and db_denydatawriter. Any database activity can be performed by members of db_ owner database role. Functions of addition or removal of Windows NT groups and database users or SQL Server database users are performed by members of db_accessadmin database role. All user tables data in the database can be seen by the members of db_datareader database role. All addition, change or deletion of data from any user table in database can be done by members of db_datawriter database role. Making data definition language commands in the database is the function of db_ddladmin database role members. Management of statement and object permissions are done by db_securityadmin database role members. Db_backupoperator database role members back up the database. Permission to select data in database can be denied by the members of db_denydatareader database role. Permission to change data in database can be denied by members of db_denydatawriter database role.