Roles are another new concept in SQL7. The are similar to the concept of groups. By assigning permissions to a role, and then adding users to the role, all users inherit the permission of that role. This greatly eases administrations. One role, the PUBLIC role, exists in all SQL 7 databases. Users are always a member of the PUBLIC role. Any permissions assigned to the PUBLIC role would apply to all users. If you have experience with SQL Server 6.5 groups, you may recall that a user could be a member of the PUBLIC group and any one other. Well, in SQL 7, a user can be a member of multiple roles, in addition to PUBLIC.
In addition to user defined roles that you create to ease administration of permissions, there are also two categories of fixed, or pre-defined, roles. They are Database Roles and Server Roles. The table below summarizes these Fixed Roles.
Fixed Server Roles
Role
Permission Description
dbcreator
Create & alter databases
diskadmin
Manage disk files
processadmin
Manage SQL Server processes, such as the ability to kill errant queries.
securityadmin
Manage and audit server logins, permission to create databases and read error logs.
serveradmin
Configure server wise settings, such as those available with the sp_configure command. Shut down server using the SHUTDOWN command.
setupadmin
Has the ability to manage linked servers and designate stored procedures to run at startup.
sysadmin
Perform any activity. They essentially override all security mechanisms.
Fixed Database Roles
Role
Permission Description
public
Maintains all default permissions
db_owner
Perform any activity within the database.
db_accessadmin
Add/remove database users, groups, and roles
db_ddladmin
Add/modify/drop database objects
db_securityadmin
Assign statement and object permissions
db_backupoperator
Backup & restore databases
db_datareader
Read data from any table
db_datawriter
Add/change/delete data from all tables
db_denydatareader
Cannot read data from any table
db_denydatawriter
Cannot change data in any table
db_dumpoperator
Can issue DBCC commands, checkpoint, and backup commands.
There is one other special type of role called the Application Role. They are similar to user defined roles in that they allow you to assign permissions to the role instead of individual users. However, they are different in implementation. Application roles are "invoked" by a client application. Once invoked, the users permissions are suspended and they then assume all permissions of the application role.
Applications roles are password protected so that they can only be invoked by an entity with knowledge of the password, typically the end user application. An application role is simply created using syntax such as:
Code :
sp_addapprole 'my_new_role', 'my_password'
The roll can then be invoked using a command such as:
Code :
sp_setapprole 'my_new_role', 'my_password'
This password will be sent in clear text across the network. Fortunately, it is possible to encrypt the password before it is send using a command syntax such as:
Code :
sp_setapprole 'my_new_role', Encrypt N 'my_password', ODBC
