Managing Users Permissions on SQL Server

Introduction

Permissions are the rights to
access the database objects. Permissions can be granted to a user or role to
allow that user or role to perform operations such as selection, insertion or
modification of data rows.

Each database object has an owner. By default, the owner is the creator of an
object, but the ownership can be transferred later after the object has been
created. In addition to the owner, the members of the sysadmin fixed server
roles have full permissions on all objects in all user and system databases.

There is also a public role. The public role is a special database role to
which each database user belongs. The public role contains default access
permissions for any user who can access the database. This database role cannot
be dropped, but it is strongly recommended not to grant superfluous permissions
to the public role, because each databases user has the public role’s
permissions.

SQL Server 2000 provides the GRANT, DENY, and REVOKE statements to give or take
away permissions from a user or role.

Permissions Types

To perform any activity in a
database, user must have the appropriate permissions. These permissions fall
into three categories, which we call permissions types:

  • Permissions to work with data and execute procedures (object
    permissions).

  • Permissions to create a database or an item in the database
    (statement permissions).

  • Permissions to utilize permissions granted to predefined roles
    (implied permissions).

SQL Server 2000 supports granting or revoking user rights to the following
permissions types:

Object Permissions

The object permissions are the
permissions to act on the database objects (such as tables, stored procedures
and views). They consist of the following permissions:

  • SELECT
    Enables a user to select or read data from a table or view. The SELECT
    permission can be applied to individual columns within a table or view, and may
    be applied to user-defined functions.

  • INSERT
    Enables a user to insert new data to a table or view.

  • DELETE
    Enables a user to delete data from a table or view.

  • UPDATE
    Enables a user to update data in a table or view. The UPDATE permission can be
    applied to individual columns within a table or view, not just the entire
    table.

  • EXECUTE
    Enables a user to execute a stored procedure.

  • DRI (declarative referential integrity)
    Enables a user to add foreign key constraints on a table.

Statement Permissions

These are the permissions to create a database or an object
in the database. These permissions are applied to the statement itself, rather
than to a specific object defined in the database. They consist of the
following permissions:

  • BACKUP DATABASE
    The BACKUP DATABASE statement is used to back up an entire database or one or
    more files or filegroups.

  • BACKUP LOG
    The BACKUP LOG statement is used to back up the transaction log.

  • CREATE DATABASE
    The CREATE DATABASE statement is used to create a new database and the files
    used to store the database.

  • CREATE DEFAULT
    The CREATE DEFAULT statement is used to create an object called a default.

  • CREATE FUNCTION
    The CREATE FUNCTION statement is used to create a user-defined function, which
    is a saved Transact-SQL routine that returns a value.

  • CREATE PROCEDURE
    The CREATE PROCEDURE statement is used to create a stored procedure, which is a
    saved collection of Transact-SQL statements.

  • CREATE RULE
    The CREATE RULE statement is used to create an object called a rule.

  • CREATE TABLE
    The CREATE TABLE statement is used to create a new table.

  • CREATE VIEW
    The CREATE VIEW statement is used to create an object called a view.

Implied Permissions

These are the permissions
granted to the predefined roles (such as fixed server roles or fixed database
roles). For example, a member of the db_owner fixed database role has full permissions
in the database.

Managing Permissions

You can use the GRANT, DENY,
and REVOKE statements to give or take away permission from a user or role.

The GRANT statement is used to give permissions to a user or role. By using the
GRANT statement, it is possible to assign permissions to both statements as
well as objects. You can use the GRANT statement with the WITH GRANT OPTION
clause to permit the user or role receiving the permission to further
grant/revoke access to other accounts.

This example grants the SELECT permission on the authors table to Alex

GRANT SELECT ON authors TO Alex

The DENY statement is used to deny a permission from a security account in the
current database and prevents the security account from inheriting the
permission through its group or role memberships. You can use the DENY
statement to deny both statements and objects permissions.

The following example denies the user Alex SELECT permissions to the authors
table:

DENY SELECT ON authors TO Alex

The REVOKE statement is used to remove a previously granted or denied
permission from a user in the current database. You can use the REVOKE
statement to remove both statements and objects permissions. You can specify
the GRANT OPTION FOR clause with the REVOKE statement to remove the WITH GRANT
OPTION permissions. Therefore, the user will have the objects permissions, but
cannot grant the permissions to other users. Specify the CASCADE clause along
with the WITH GRANT OPTION clause, if the permissions being revoked were originally
granted using the WITH GRANT OPTION setting.

The following example revokes SELECT permissions to the authors table from the
user, Alex:

REVOKE SELECT ON authors TO Alex

Use the WITH GRANT OPTION setting very carefully, because in this case users can
grant permissions to the objects to other users and it will be more difficult
to manage security.

Do not grant the superfluous permissions to the public role, because each
database user has the public role’s permissions.

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Latest Articles