Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 19, 2001

Understanding SQL Server Roles

By Alexander Chigrik


Introduction
Server Roles
Database Roles
  • Fixed Database Roles
  • Public Role
  • User-Defined Database Roles
  • Application Roles

    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)


    » See All Articles by Columnist Alexander Chigrik




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM