DCL (Data Control Language). These SQL statements control the security and permissions of the objects or parts of the database(s). DCL SQL statements are also more part of the DBMS and have large differences between the SQL variations.

Intro

Every user connects to SQL Server via a login. A login may be assigned one or more Server Roles, each with varying degrees of permissions. Each login is mapped to a different user account on each database. Each user account may be assigned one or more database roles, each with varying degrees of permissions.

The name of logins, users, roles, and passwords must obey these syntax rules:

Logins

To connect to SQL Server, a user has to have an authenticated Login. A Login must have been created within SQL Server. This is done by one of two means:

Each instance of SQL Server always has a special login called sa (system administrator) who always has the server role of sysadmin and the database role of db_owner for every database in the instance of SQL Server.

Users

Each database has its own list of users, each of which is associated with a login by the sp_grantdbaccess system stored procedure.

Auditing occurs at the user level, not the group level, so if Joe of the MARKETING group accesses a database, the audit log will note Joe's activities.

Each database always has a special user called dbo (database owner) who always has the database role of db_owner. All members of the server role of sysadmin are always members of each dbo user in each database.

A special user called guest can be created for any database but is required for the master and tembdb databases. This user account is used for any login that is not mapped to a user account on a database.

Roles

Once connected, the login/user has permissions that depend on his security roles. Roles can contain users, groups, and even other roles (thus allowing non-circular nesting of roles). 

Server Roles. Predefined sets of permissions that apply to a login for the whole server.

Database Roles. These are predefined and custom sets of permissions that apply to a user for all objects within a particular databases within SQL Server.  There are two kinds of Database Roles:

Permissions

Every database object in SQL Server is owned by a user. Permissions apply to statements allowed by users

Here is the syntax to GRANT permission, DENY permission, and REVOKE previously granted or denied permissions:

--For statements:
{GRANT | DENY | REVOKE} {ALL | Statement [, ...]}
TO SecurityAccount [, ...]

--Where Statement can be: CREATE DATABASE, CREATE DEFAULT, --CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, --CREATE VIEW, BACKUP DATABASE, or BACKUP LOG
--For objects:
{GRANT | DENY | REVOKE [GRANT OPTION FOR]}
    {ALL [PRIVILEGES] | Permission [, ...]}
    {   [(Column [, ...])] ON {Table | View}     |
        ON {Table | View} [(Column [, ...])]     |
        ON {StoredProcedure | ExtendedProcedure} | 
        ON {UserDefinedFunction} 
    }

--For GRANT
TO SecurityAccount [, ...]
[WITH GRANT OPTION]
[AS {Group | Role}]

--For DENY
TO SecurityAccount [, ...]
[CASCADE] 

--For REVOKE
{TO | FROM} SecurityAccount [, ...] 
[CASCADE]
[AS {Group | Role}]

Objects created by members of the sysadmin server role make a table are owned by the dbo of each database (eg dbo.table1). Objects created by others (including those who are members of the db_owner role) are owned by that user (eg nonSysadminUser.table1 ).

Permissions are additive, ie a user, group, or role usually acquires more permissions with membership in more groups and roles. However if a permission has been specifically denied for a particular user, group, or role, then that denial takes precedence.

EG GRANT

GRANT CREATE DATABASE, CREATE TABLE
TO Julia, Connie, [FooLAN\York]

EG GRANT ownership chain

SQL Sever checks for breaks in the ownership chain. EG: If Amos owns Table1 and gives the Sales group permission to SELECT on Table1 but not ownership, then Betsy who is a member of Sales cannot give Chuck permission to use Table1 unless Amos originally used the WITH GRANT OPTION keywords.

--Run by Amos
GRANT SELECT ON Table1 TO Sales WITH GRANT OPTION

Furthermore, Betsy can only give Chuck permission if she uses the AS keywords since her group has the GRANT power and her specifically.

--Run by Betsy
GRANT SELECT ON Table1 TO Chuck AS Sales

Sessions

Every session (aka connection) to an instance of SQL Server has a SPID (Server Process ID). Here is some general info about sessions.

Page Modified: (Hand noted: 2007-10-12 18:48:42Z) (Auto noted: 2007-11-17 06:44:04Z)