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.


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:

  • 1-128 characters long.
  • Letters, numbers, and symbols are allowed.
  • Cannot contain a backslash (\) or be NULL or an empty string("" ).
  • Cannot already exist for its given scope.
  • Avoid spaces ( ), dollar signs ($), or the at character (@). If these are used, then the name must be delimited using double quotation marks ("), or square brackets ([ and ] ) in SQL statements.


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:

  • Windows NT authentication (aka trusted connection). Authenticate specific NT users and groups. If the user was logged on to a NT network prior to connecting to SQL Server, then this step is invisible to the user since the user was previously authenticated by the network. Note that the Login user name and the NT user name don't have to be the same but they might as well. Usually local groups are mapped to appropriate Server and Database Roles, however global groups and users can be mapped directly to specific roles. Windows logins to SQL Server are created with the sp_grantlogin system stored procedure; the default database for these logins can be set with the sp_defaultdb system stored procedure.
  • SQL Server authentication (aka non-trusted connection). Authenticate users, especially if neither the user or the server is set up for NT authentication. The user must supply the username and password that is stored in the sysxlogins system table of the master database of SQL Server. SQL Server logins to SQL Server are created with the sp_addlogin system stored procedure; the same procedure is used to set up the default database for the login.

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.


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.


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.
  • sysadmin. Perform any activity in this instance of SQL Server. This contains the local NT BUILTIN\Administrators group by default. The login sa always has this role. Encompasses all the other Server Roles.
    • serveradmin . Configure server-wide configurations and shut down the server.
    • setupadmin . Manage linked servers and startup procedures.
    • securityadmin . Manage logins and CREATE DATABASE permissions; read error logs.
    • processadmin . Manage processes running in the server.
    • dbcreator . Create, alter, and restore databases.
    • diskadmin . Manage disk files.
    • bulkadmin . Execute BULK INSERT statements.

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:

  • Standard. Contains members, ie users.
    • Custom_Standard_Roles. A custom set of permissions apply to its members for the database.
    • Fixed Database Roles. Predefined sets of permissions that apply to its members for the database.
      • public . Special role that exists in all the databases within the server. All users are automatically a member of this group, ie it functions similarly to the local NT Everyone group. The permissions for this group can be modified.
      • db_owner. All permissions in this database. The login sa always has this role in every database. Each database always has a user called dbo with this role. Encompasses all the other Standard Fixed Database Roles.
        • db_accessadmin . Add or remove users.
        • db_datareader . Read all data from all user tables.
        • db_datawriter . Add, delete, or edit data in all user tables.
        • db_ddladmin . Run any Data Definition Language command, but cannot issue GRANT, REVOKE, or DENY statements.
        • db_securityadmin . Modify role membership and manage permissions.
        • db_backupoperator . Back up database, including DBCC, CHECKPOINT, and BACKUP statements.
        • db_denydatareader . Cannot read any data in user tables.
        • db_denydatawriter . Cannot add, delete, or edit data in all user tables.
  • Custom_Application_Roles. Contains no users. A custom set of permissions will apply for those who supply the role's password via sp_setapprole. The application assumes responsibility for user authentication and permissions. EG: A department might be able to access a database through an application but not through SQL Server Query Analyzer.


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:
--For objects:
    {ALL [PRIVILEGES] | Permission [, ...]}
    {   [(Column [, ...])] ON {Table | View}     |
        ON {Table | View} [(Column [, ...])]     |
        ON {StoredProcedure | ExtendedProcedure} |
        ON {UserDefinedFunction}

TO SecurityAccount [, ...]
[AS {Group | Role}]

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

{TO | FROM} SecurityAccount [, ...]
[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.


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

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


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

  • The information about each session is stored in the master.dbo.sysprocesses table.
  • The master.dbo.sysprocesses.context_info column is available for storing up to 128 bytes of binary info and is easily set with SET CONTEXT INFO. EG:
    SET CONTEXT_INFO 0xHexadecimal
  • The SPID is easily acquired via the nullary function @@SPID. EG:
    SELECT context_info FROM master.dbo.sysprocesses WHERE spid=@@SPID

GeorgeHernandez.comSome rights reserved