SQLServerCentral Article

Roles in SQL Server 7.0

,


Introduction

Roles are the new SQL Server 7.0 feature, which was not available in

the previous versions. SQL Server roles act like Windows NT local

groups.

SQL Server 7.0 supports several different types of roles. There are:

  • server roles

  • database roles

  • application roles

In this article, I want to tell you about each kind of roles, about

how you can add new role, how you can drop existing role, how you

can return information about the roles, and so on.

Server Roles

Server roles are defined at the server level and exist outside of users

databases. There are only fixed server roles, so you cannot add, delete

or modify server role. You can only add users as a member of a server

roles.

There are seven fixed server roles:

  • sysadmin

  • serveradmin

  • setupadmin

  • securityadmin

  • processadmin

  • dbcreator

  • diskadmin

The members of sysadmin server role can perform any activity in

SQL Server 7.0 and have completes control over all database functions.

The members of serveradmin server role can change server

configuration

parameters and shut down the server.

The members of setupadmin server role can manage linked servers (add

or remove linked servers), manage replication, manage extended stored

procedures, and execute some system stored procedures, such as

sp_serveroption.

The members of securityadmin server role can create and manage server

logins and auditing, and read error logs.

The members of processadmin server role can manage the processes

running in SQL Server.

The members of dbcreator server role can create, alter, and resize

databases.

The members of diskadmin server role can manage disk files.

To add a login as a member of a fixed server role, you

can use

sp_addsrvrolemember system stored procedure.

This is the syntax:

sp_addsrvrolemember [@loginame =] 'login', [@rolename =] 'role'

where

@loginame
is a SQL Server login or a Windows NT user account.
@rolename
is the name of the fixed server role.

See this link for more information

sp_addsrvrolemember

(T-SQL)

To remove a SQL Server login or a Windows NT user or

group from a fixed

server role, you can use sp_dropsrvrolemember system stored

procedure.

This is the syntax:

sp_dropsrvrolemember [@loginame =] 'login', [@rolename =] 'role'

where

@loginame
is the name of a login to remove.
@rolename
is the name of the fixed server role.

See this link for more information

sp_dropsrvrolemember

(T-SQL)

To return a list of the SQL Server fixed server roles,

you can use

sp_helpsrvrole system stored procedure.

This is the syntax:

sp_helpsrvrole [[@srvrolename =] 'role']

where

@srvrolename
is the name of the fixed server role.

See this link for more information

sp_helpsrvrole

(T-SQL)

Database Roles

In SQL Server 6.5 you can use database groups to simplify management

of a large number of database users. For example, you can use database

groups to grant and revoke permissions to more than one user at the

same time.

But database groups are no longer supported in SQL Server 7.0.

SQL Server database roles act like SQL Server 6.5 database groups,

but roles have some improvements: in SQL Server 6.5 each user can be

a member of only one group (in addition to public group), but in

SQL Server 7.0 each user can belong to many roles and the result users

permissions are combined for all roles they're members of.

There are three kinds of the database roles:

  • Fixed Database Roles

  • Public Role

  • User-Defined Database Roles

Fixed Database Roles

Fixed database roles are defined at the database level and exist in

each database.

You cannot add, delete or modify fixed database roles. You can only

add users as a member of a fixed database roles.

There are nine fixed database roles:

  • db_owner

  • db_accessadmin

  • db_datareader

  • db_datawriter

  • db_ddladmin

  • db_securityadmin

  • db_backupoperator

  • db_denydatareader

  • db_denydatawriter

The members of db_owner database role can perform any activity in

the database.

The members of db_accessadmin database role can add or remove Windows

NT

groups, users or SQL Server users in the database.

The members of db_datareader database role can see any data from all

user tables in the database.

The members of db_datawriter database role can add, change, or delete

data from all user tables in the database.

The members of db_ddladmin database role can make any data definition

language commands in the database.

The members of db_securityadmin database role can manage statement

and object permissions in the database.

The members of db_backupoperator database role can back up the

database.

The members of db_denydatareader database role can deny permission to

select data in the database.

The members of db_denydatawriter database role can deny permission to

change data in the database.

To add a security account as a member of an existing

SQL Server

database role in the current database, you can use sp_addrolemember

system stored procedure.

This is the syntax:

sp_addrolemember [@rolename =] 'role',

[@membername =] 'security_account'

where

@rolename
is the name of the database role.
@membername
is the name of the security account.

Any member of a fixed database role can add other users to this role.

See this link for more information

sp_addrolemember

(T-SQL)

To remove a security account from a SQL Server role in

the current

database, you can use sp_droprolemember system stored procedure.

This is the syntax:

sp_droprolemember [@rolename =] 'role',

[@membername =] 'security_account'

where

@rolename
is the name of the role.
@membername
is the name of the security account.

See this link for more information

sp_droprolemember

(T-SQL)

To return information about the members of a role in

the current

database, you can use sp_helprolemember system stored procedure.

This is the syntax:

sp_helprolemember [[@rolename =] 'role']

where

@rolename
is the name of a role in the current database.

See this link for more information

sp_helprolemember

(T-SQL)

Public Role

The public role is a special database role to which every database

user belongs. The public role contain default access permissions

for any user who can access the database. This database role cannot

be dropped.

User-Defined Database Roles

Although the built-in database roles handle permissions for common

database management tasks, it's likely that you will want to group

users who have access to perform specific database functions.

To create a new SQL Server role in the current

database, you can use

sp_addrole system stored procedure.

This is the syntax:

sp_addrole [@rolename =] 'role' [,[@ownername =] 'owner']

where

@rolename
is the name of the database role.
@ownername
is the owner of the new role.

See this link for more information

sp_addrole

(T-SQL)

To remove a SQL Server role from the current database,

you can use

sp_droprole system stored procedure.

This is the syntax:

sp_droprole [@rolename =] 'role'

where

@rolename
is the name of the role.

See this link for more information

sp_droprole

(T-SQL)

Application Roles

Application roles are the SQL Server roles created to support the

security needs of an application. Often database applications

enforce their own security based on the application logic.

For example, you can use application role with its own password

to allow the particular user to obtain and modify any data only

during specific hours. So, you can realize more complex security

management within the application logic.

To add a special type of role in the current database,

which is used

for application security, you can use sp_addapprole stored

procedure.

This is the syntax:

sp_addapprole [@rolename =] 'role', [@password =] 'password'

where

@rolename
is the name of the application role.
@password
is the password for the new application role.

See this link for more information

sp_addapprole

(T-SQL)

To remove an application role from the current

database, you can use

sp_dropapprole system stored procedure.

This is the syntax:

sp_dropapprole [@rolename =] 'role'

where

@rolename
is the name of the application role.

See this link for more information

sp_dropapprole

(T-SQL)

To change the password of an application role in the

current database,

you can use sp_approlepassword system stored procedure.

This is the syntax:

sp_approlepassword [@rolename =] 'role', [@newpwd =] 'password'

where

@rolename
is the name of the application role.
@newpwd
is the new password for the application role.

See this link for more information

sp_approlepassword

(T-SQL)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating