SQL Server: Grant/Revoke Privileges

Learn how to grant and revoke privileges in SQL Server (Transact-SQL) with syntax and examples.

Description

You can GRANT and REVOKE privileges on various database objects in SQL Server. We'll look at how to grant and revoke privileges on tables in SQL Server.

Grant Privileges on Table

You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.

Syntax

The syntax for granting privileges on a table in SQL Server is:

GRANT privileges ON object TO user;

privileges

The privileges to assign. It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
ALL	ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES.

object

The name of the database object that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.

user

The name of the user that will be granted these privileges.

Example

Let's look at some examples of how to grant privileges on tables in SQL Server.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called employees to a user name smithj, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO johnny;

You can also use the ALL keyword to indicate that you wish to grant the ANSI-92 permissions (ie: SELECT, INSERT, UPDATE, DELETE, and REFERENCES) to a user named johnny. For example:

GRANT ALL ON employees TO johnny;

If you wanted to grant only SELECT access on the employees table to all users, you could grant the privileges to the public role. For example:

GRANT SELECT ON employees TO public;

Revoke Privileges on Table

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.

Syntax

The syntax for revoking privileges on a table in SQL Server is:

REVOKE privileges ON object FROM user;

privileges

It is the privileges to assign. It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
ALL	ALL does not revoke all permissions for the table. Rather, it revokes the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES.

object

The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user

The name of the user that will have these privileges revoked.

Example

Let's look at some examples of how to revoke privileges on tables in SQL Server.

For example, if you wanted to revoke DELETE privileges on a table called employees from a user named anderson, you would run the following REVOKE statement:

REVOKE DELETE ON employees FROM king;

If you wanted to revoke ALL ANSI-92 permissions (ie: SELECT, INSERT, UPDATE, DELETE, and REFERENCES) on a table for a user named king, you could use the ALL keyword as follows:

REVOKE ALL ON employees FROM king;

If you had granted SELECT privileges to the public role (ie: all users) on the employees table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE SELECT ON employees FROM public;

 

SQL Server: Indexes

Learn how to create, rename and drop indexes in SQL Server with syntax and examples.

What is an Index in SQL Server?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

Create an Index

Syntax

The syntax for creating an index in SQL Server (Transact-SQL) is:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
  ON table_name ( column1 [ASC | DESC ], ...  column_n [ ASC | DESC ] )
  [ INCLUDE ( column1, ... column_n ) ]
  [ WHERE condition ]
  [ WITH ( PAD_INDEX = { ON | OFF }
         | FILLFACTOR = fillfactor
         | SORT_IN_TEMPDB = { ON | OFF }
         | IGNORE_DUP_KEY = { ON | OFF }
         | STATISTICS_NORECOMPUTE = { ON | OFF }
         | STATISTICS_INCREMENTAL = { ON | OFF }
         | DROP_EXISTING = { ON | OFF }
         | ONLINE = { ON | OFF }
         | ALLOW_ROW_LOCKS = { ON | OFF }
         | ALLOW_PAGE_LOCKS = { ON | OFF }
         | MAXDOP = max_degree
         | DATA_COMPRESSION = { NONE | PAGE | ROW }
            [ ON PARTITIONS ( { number | range } ]
  [ ON partition_scheme ( column )
  | ON filegroup
  | ON default_filegroup ]
  [ FILESTREAM_ON { filegroup | partition_scheme };

UNIQUE

Optional. Indicates that the combination of values in the indexed columns must be unique.

CLUSTERED

Optional. Indicates that the logical order determines the physical order of the rows in the table.

NONCLUSTERED

Optional. Indicates that the logical order does not determine the physical order of the rows in the table.

index_name

The name of the index to create.

table_name

The name of the table or view on which the index is to be created.

column1, ... column_n

The columns to base the index.

ASC | DESC

The sort order for each of the columns.

INCLUDE ( column1, ... column_n )

Optional. The columns that are not key columns to add to the leaf level of the nonclustered index.

WHERE condition

Optional. The condition to determine which rows to include in the index.

ON partition_scheme ( column )

Optional. Indicates that the partition schema determines the filegroups in which the partitions will be mapped.

ON filegroup

Optional. Indicates that the index will be created on the specified filegroup.

ON default_filegroup

Optional. Indicates the default filegroup.

FILESTREAM_ON { filegroup | partition_scheme }

Optional. Indicates where to place the FILESTREAM data for a clustered index.

Index Example

Let's look at an example of how to create an index in SQL Server (Transact-SQL).

For example:

CREATE INDEX contacts_idx
  ON contacts (last_name);

In this example, we've created an index on the contacts table called contacts_idx. It consists of only one field - the last_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX contacts_idx
  ON contacts (last_name, first_name);

In this example, we've created an index on the contacts table called contacts_idx but this time, it consists of the last_name and first_name fields.

Since we have not specified ASC | DESC to each of the columns, the index is created with each of the fields in ascending order. We could modify our example and change the sort orders to descending as follows:

CREATE INDEX contacts_idx
  ON contacts (last_name DESC, first_name DESC);

This CREATE INDEX example will create the contacts_idx index with the last_name sorted in descending order and the first_name sorted in descending order.

UNIQUE Index Example

Next, let's look at an example of how to create a unique index in SQL Server (Transact-SQL).

For example:

CREATE UNIQUE INDEX contacts_uidx
  ON contacts (last_name, first_name);

This example would create an index called contacts_uidx on that contacts table that consists of the last_name and first_name fields, but also ensures that the there are only unique combinations of the two fields.

You could modify this example further to make the unique index also clustered so that the physical order of the rows in the table is determined by the logical order of the index.

For example:

CREATE UNIQUE CLUSTERED INDEX contacts_uidx
  ON contacts (last_name, first_name);

This example creates an index called contacts_uidx that is a unique index based on the last_name and first_name fields and the index is also clustered which changes the physical order of the rows in the table.

Rename an Index

Syntax

The syntax for renaming an index in SQL Server (Transact-SQL) is:

sp_rename 'table_name.old_index_name', 'new_index_name', 'INDEX';

table_name

The name of the table where the index has been created.

old_index_name

The name of the index that you wish to rename.

new_index_name

The new name for the index.

Example

sp_rename 'contacts.contacts_idx', 'contacts_index_cname', 'INDEX';

In this example, we're renaming the index on the contacts table called contacts_idx to contacts_index_cname.

Drop an Index

Syntax

The syntax for dropping an index in SQL Server is:

DROP INDEX table_name.index_name;

table_name

The name of the table where the index has been created.

index_name

The name of the index to drop.

Example

Let's look at example of how to drop an index in SQL Server (Transact-SQL).

For example:

DROP INDEX contacts.contacts_idx;

In this example, we're dropping an index called supplier_idx.

 

SQL Server: Check Constraints

This SQL Server tutorial explains how to use the check constraints in SQL Server (Transact-SQL) with syntax and examples.

What is a check constraint in SQL Server?

A check constraint in SQL Server (Transact-SQL) allows you to specify a condition on each row in a table.

Note

• A check constraint can NOT be defined on a SQL View.
• The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
• A check constraint can NOT include a Subquery.
• A check constraint can be defined in either a CREATE TABLE statement or a ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a check constraint using a CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],

  ...

  CONSTRAINT constraint_name
    CHECK [ NOT FOR REPLICATION ] (column_name condition)

);

table_name

The name of the table that you wish to create with a check constraint.

constraint_name

The name to assign to the check constraint.

column_name

The column in the table that the check constraint applies to.

condition

The condition that must be met for the check constraint to succeed.

Example

Let's look at an example of how to use the CREATE TABLE statement in SQL Server to create a check constraint.

For example:

CREATE TABLE employees
( employee_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT check_employee_id
    CHECK (employee_id BETWEEN 1 and 10000)
);

In this first example, we've created a check constraint on the employees table called check_employee_id. This constraint ensures that the employee_id field contains values between 1 and 10000.

Let's take a look at another example.

CREATE TABLE employees
( employee_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT check_salary
    CHECK (salary > 0)
);

In this second example, we've created a check constraint on the employees table called check_salary. This constraint ensures that the salary value is greater than 0.

Using an ALTER TABLE statement

The syntax for creating a check constraint in an ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
  CHECK (column_name condition);

table_name

The name of the table that you wish to modify by adding a check constraint.

constraint_name

The name to assign to the check constraint.

column_name

The column in the table that the check constraint applies to.

condition

The condition that must be met for the check constraint to succeed.

Example

Let's look at an example of how to use the ALTER TABLE statement to create a check constraint in SQL Server.

For example:

ALTER TABLE employees
ADD CONSTRAINT check_last_name
  CHECK (last_name IN ('Smith', 'Anderson', 'Jones'));

In this example, we've created a check constraint on the existing employees table called check_last_name. It ensures that the last_name field only contains the following values: Smith, Anderson, or Jones.

Drop a Check Constraint

The syntax for dropping a check constraint in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

table_name

The name of the table that you wish to drop the check constraint.

constraint_name

The name of the check constraint to remove.

Example

Let's look at an example of how to drop a check constraint in SQL Server.

For example:

ALTER TABLE employees
DROP CONSTRAINT check_last_name;

In this SQL Server example, we are dropping a check constraint on the employees table called check_last_name.

Enable a Check Constraint

The syntax for enabling a check constraint in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
WITH CHECK CHECK CONSTRAINT constraint_name;

table_name

The name of the table that you wish to enable the check constraint.

constraint_name

The name of the check constraint to enable.

Example

Let's look at an example of how to enable a check constraint in SQL Server.

For example:

ALTER TABLE employees
WITH CHECK CHECK CONSTRAINT check_salary;

In this example, we are enabling a check constraint on the employees table called check_salary.

Disable a Check Constraint

The syntax for disabling a check constraint in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;

table_name

The name of the table that you wish to disable the check constraint.

constraint_name

The name of the check constraint to disable.

Example

Let's look at an example of how to disable a check constraint in SQL Server.

For example:

ALTER TABLE employees
NOCHECK CONSTRAINT check_salary;

In this SQL Server example, we are disabling a check constraint on the employees table called check_salary.

 

SQL Server: Unique Constraints

This SQL Server tutorial explains how to create, add, and drop unique constraints in SQL Server with syntax and examples.

What is a unique constraint in SQL Server?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in SQL Server is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the unique constraint.

uc_col1, uc_col2, ... uc_col_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in SQL Server using the CREATE TABLE statement.

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  employee_number INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT employees_unique UNIQUE (employee_number)
);

In this example, we've created a unique constraint on the employees table called employees_unique. It consists of only one field which is the employee_number.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  employee_number INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT employees_unique UNIQUE (last_name, first_name)
);

Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in SQL Server is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

table_name

The name of the table to modify. This is the table that you wish to add a unique constraint to.

constraint_name

The name of the unique constraint.

column1, column2, ... column_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in SQL Server using the ALTER TABLE statement.

ALTER TABLE employees
ADD CONSTRAINT employees_unique UNIQUE (employee_number);

In this example, we've created a unique constraint on the existing employees table called employees_unique. It consists of the field called employee_number.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE employees
ADD CONSTRAINT employee_name_unique UNIQUE (last_name, first_name);

Drop Unique Constraint

The syntax for dropping a unique constraint in SQL Server is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

table_name

The name of the table to modify. This is the table whose unique constraint you wish to remove.

constraint_name

The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in SQL Server.

ALTER TABLE employees
DROP CONSTRAINT employees_unique;

In this example, we're dropping a unique constraint on the employees table called employees_unique.

 

SQL Server: Foreign Key Topics

The following is a list of topics that explain how to use Foreign Keys in SQL Server (Transact-SQL):

Create Foreign Keys

• Create Foreign Keys
• Create Foreign Keys with cascade delete
• Create Foreign Keys with set null on delete

Drop Foreign Keys

• Drop a Foreign Key

Disable/Enable Foreign Keys

• Disable a Foreign Key
• Enable a Foreign Key

 

SQL Server: Primary Keys

Learn how to create, drop, disable, and enable a primary key in SQL Server (Transact-SQL) with syntax and examples.

What is a primary key in SQL Server?

In SQL Server (Transact-SQL), a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Create Primary Key - Using CREATE TABLE statement

You can create a primary key in SQL Server with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ] [ PRIMARY KEY ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

OR

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in SQL Server (Transact-SQL).

For example:

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  salary MONEY
);

In this example, we've created a primary key on the employees table that is made up of only one field - the employee_id field.

We could have also created the primary key as follows:

CREATE TABLE employees
( employee_id INT,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  salary MONEY,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

Next, let's look at how to create a primary key in SQL Server (Transact-SQL) where the primary key is a composite key that is made up of more than one field.

For example:

CREATE TABLE employees
( last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  salary MONEY,
  CONSTRAINT employees_pk PRIMARY KEY (last_name, first_name)
);

In this example, we have created a primary key that is made up of two columns, the last_name and the first_name columns. These two fields would uniquely define the records in the employees table.

Create Primary Key - Using ALTER TABLE statement

You can create a primary key in SQL Server (Transact-SQL) with the ALTER TABLE statement. However, you can only use the ALTER TABLE statement to create a primary key on column(s) that are already defined as NOT NULL. If the column(s) allow NULL values, you will not be able to add the primary key without dropping and recreating the table.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).

For example:

ALTER TABLE employees
ADD CONSTRAINT employees_pk PRIMARY KEY (employee_id);

In this example, we've created a primary key on the existing employees table called employees_pk. It consists of the field called employee_id. Again, it is important to note that the employee_id must already be defined as a NOT NULL field for this ALTER TABLE statement to succeed. If the employee_id column allows NULL values, the employees table will have to be dropped and recreated with employee_id defined as a NOT NULL field for the primary key to be created.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE employees
ADD CONSTRAINT employees_pk PRIMARY KEY (last_name, first_name);

In this example, we've created a primary key on the employees table that consists of the last_name and first_name fields. The last_name and first_name fields must be defined as NOT NULL in the employees table for this primary key to be created succesfully.

Drop Primary Key

You can drop a primary key in SQL Server using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).

For example:

ALTER TABLE employees
DROP CONSTRAINT employees_pk;

In this example, we would drop the primary key on the employees table called employees_pk.

Disable Primary Key

You can disable a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).

Syntax

The syntax to disable a primary key using the ALTER INDEX statement in SQL Server (Transact-SQL) is:

ALTER INDEX constraint_name ON table_name
DISABLE;

Example

Let's look at an example of how to disable a primary using the ALTER INDEX statement in SQL Server (Transact-SQL).

For example:

ALTER INDEX employees_pk ON employees
DISABLE;

In this example, we would disable the primary key on the employees table called employees_pk.

Enable Primary Key

You can enable a primary key using the ALTER INDEX statement in SQL Server (Transact-SQL).

Syntax

The syntax to enable a primary key using the ALTER INDEX statement in SQL Server (Transact-SQL) is:

ALTER INDEX constraint_name ON table_name
REBUILD;

Example

Let's look at an example of how to enable a primary key using the ALTER INDEX statement in SQL Server.

ALTER INDEX employees_pk ON employees
REBUILD;

In this example, we're enabling a primary key on the employees table called employees_pk.