Database Roles

Of the three database roles, fixed database roles have already been discussed. Besides fixed database roles, each database user belongs to a special database role called public role. Any user wanting to access the database can have default access permissions contained in public role, a database role that cannot be done without. The third type of database roles is the User-defined database roles.

User-defined Database Roles

Common database management tasks permissions are handled by database roles that are built-in. But when group users are required to have access to performing of specific database functions, user-defined database roles is required. A new SQL Server role can be created in the current database by using sp_addrole system stored procedure. The syntax is: sp_addrole [@rolename =] 'role' [,[@ownername =] 'owner'] where @rolename  is the database role name and @ownername is the new role owner. Similarly for removal of a SQL Server role from the current database, the sp_droprole system stored procedure can be used. The syntax is:  sp_droprole [@rolename =] 'role' where @rolename is the role name.

Application Roles

The SQL Server roles created for supporting an application’s security needs are called application roles. It so happens that depending on the application logic, enforcement of their own security is performed by database applications. Like for permitting a particular user to get and modify data only at specified hours, an application role with password of its own can be used. Thus application roles enable within the application logic, management of more complex security. A special type of role used for application security can be added in the current database by using the sp_addapprole system stored procedure. The syntax is: sp_addapprole [@rolename =] 'role', [@password =] 'password' where @rolename is the application role name and @password is the new application role password. Similarly for removal of an application role from current database, the sp_dropapprole system stored procedure can be used. The syntax is: sp_dropapprole [@rolename =] 'role' where @rolename is the application role name.