TechTutorials - Free Computer Tutorials  

SQL Server 7.0 Roles 

Added: 07/01/2000, Hits: 2,596, Rating: 0, Comments: 0, Votes: 0
Add To Favorites | Comment on this article
By Brian Talbert

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
RolePermission Description
dbcreatorCreate & alter databases
diskadminManage disk files
processadminManage SQL Server processes, such as the ability to kill errant queries.
securityadminManage and audit server logins, permission to create databases and read error logs.
serveradminConfigure server wise settings, such as those available with the sp_configure command. Shut down server using the SHUTDOWN command.
setupadminHas the ability to manage linked servers and designate stored procedures to run at startup.
sysadminPerform any activity. They essentially override all security mechanisms.
Fixed Database Roles
RolePermission Description
publicMaintains all default permissions
db_ownerPerform any activity within the database.
db_accessadminAdd/remove database users, groups, and roles
db_ddladminAdd/modify/drop database objects
db_securityadminAssign statement and object permissions
db_backupoperatorBackup & restore databases
db_datareaderRead data from any table
db_datawriterAdd/change/delete data from all tables
db_denydatareaderCannot read data from any table
db_denydatawriterCannot change data in any table
db_dumpoperatorCan 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

Comments (0)

Be the first to comment on this article

Related Items

7 Seconds Resources, Inc.

IT Showcase