Pass Your Microsoft Exams Quickly and Easily. Click Here!
 
 
» Lessons:
Overview of SQL Security
Logins
Database access
Roles
Permissions

  Lesson 4 :  Roles.

There are two kinds of roles, server roles and database roles.

Roles let you:

  • Assign permissions to the role and then assign a user to the role
  • Have a hierarchy of security, with a role as a member of another role
  • Change the role that the sa login belongs to.

Server Roles

Server roles are fixed roles provided by SQL Server. The fixed roles are:

  • sysadmin - this role is not restricted and can perform any task in SQL Server.
  • securityadmin - this role can administer logins.
  • serveradmin - this role can change server settings.
  • setupadmin - this role can add and delete linked servers.
  • processadmin - this role can administer processes.
  • diskadmin - this role can administer disk files.
  • dbcreator - this role can create or alter databases.

Here are some stored procedures that you will want to experiment with:

  • sp_addsrvrolemember @loginame = 'mylogin', @rolename = 'role' - assigns a login to a role.
  • sp_dropsrvrolemember @loginame = 'mylogin', @rolename = 'role' - unassigns a login from a role.
  • sp_helpsrvrole - to get a list of the server roles, with descriptions.
  • sp_helpsrvrolemember - to get a list of the server roles and the logins that are assigned to each one.
  • sp_srvrolepermission - to get a list of roles and the permissions assigned to each role.

Database Roles

You can set roles up such that the roles a login is assigned to differ depending on which database the login is using.

To view information about database roles, try out these:

  • sp_helprole @rolename = 'myrole' - this displays the SQL Server fixed database roles, user defined roles and application roles (see below).
  • sp_helprolemember @rolename = 'myrole' - this displays the fixed database roles and user defined roles but not application roles.

    1. Fixed Database Roles.

    Similar to the SQL Server fixed Server Roles, there are a set of fixed Database Roles:

    • db_owner - can administer the database and its security.
    • db_accessadmin - can add or delete database users.
    • db_securityadmin - can administer database roles and permissions.
    • db_ddladmin - can add, change or drop database objects.
    • db_backupoperator - can back up the database.
    • db_datareader - can view any data in the database.
    • db_datawriter - can edit any data in the database.
    • db_denydatareader - can deny read access to data in the database.
    • db_denydatawriter - can deny write access to data in the database.

    2. User-Defined Database Roles

    As well as the roles provided by SQL Server, you can define your own roles, using:

    sp_addrole @rolename = 'myrole', @ownername = 'owner'

    You can delete your own roles using:

    sp_droprole @rolename = 'myrole'

    You can't:

    • Drop any of the SQL Server fixed database roles.
    • Drop a role that has members.
    • Drop a role that owns objects.

    Once you've created a role, you'll want to add one or more users to it:

    sp_addrolemember @rolename = 'myrole', @membername = 'myuser'

    In the above example, 'myuser' could be:

    • A SQL Server database user.
    • An NT user.
    • An NT group.
    • A SQL Server role.

    The last item in the list above is the key to setting up a security hierarchy - you can set up several user-defined database roles with different permissions, and assign more powerful roles to the less powerful roles, so that they can do whatever the less powerful role can do, PLUS whatever extra permissions you assign to them.

    Each database has a special role named PUBLIC. All database users belong to this role and you can't remove or assign users. It is up to you to assign default permissions to the PUBLIC role, whatever permissions you want to be available to every user in the database.

    3. Application Roles.

    Application roles don't have members! Users invoke the permissions of the application role by using the application. Application roles are useful if you want users to be able to access data through an application only, and not directly, for instance through the query manager.

    Here are the stored procedures you need to know - again, please go and play with them!

    • To add an application role:
      sp_addapprole @rolename = 'myapprole', @password = 'mypassword'
    • To activate an application role:
      sp_setapprole @rolename = 'myrole', @password = 'mypassword'
    • To delete an application role:
      sp_dropapprole @rolename = 'myrole'

Pass Your Microsoft Exams Quickly and Easily. Click Here!


IT banner exchange