SQL Server/T-SQL Tutorial/User Role/GRANT

A GRANT statement that grants permission to execute a stored procedure

GRANT EXECUTE
ON spBillingReport
TO [Payroll\MarkThomas], JohnDoe, TomAaron

A GRANT statement that grants permission to run action queries

GRANT INSERT, UPDATE, DELETE
ON Billings
TO [Accounting\JaneSmith]

A GRANT statement that grants SELECT permission to specific columns

GRANT SELECT
ON Bankers (BankerName,BankerAddress1,BankerCity,BankerState,BankerZipCode)
TO TomAaron, [Payroll\MarkThomas]

All permissions applicable to the specified securable will be granted to the specified principal.

GRANT ALL TO mary

Example of GRANT EXEC Statement

GRANT EXEC on usp_ProcName to
  YourName,
  Tom

GRANT CREATE FUNCTION TO mary

GRANT CREATE FUNCTION TO mary

GRANT CREATE TABLE, CREATE PROCEDURE TO peter, paul, mary

GRANT CREATE TABLE, CREATE PROCEDURE TO peter, paul, mary

Granting Permission on a Column in a View

28>
29> CREATE TABLE employee(
30>    id          INTEGER NOT NULL PRIMARY KEY,
31>    first_name  VARCHAR(10),
32>    last_name   VARCHAR(10),
33>    salary      DECIMAL(10,2),
34>    start_Date  DATETIME,
35>    region      VARCHAR(10),
36>    city        VARCHAR(20),
37>    managerid   INTEGER
38> );
39> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3>
4> CREATE VIEW MyView
5> AS
6> SELECT
7>   id
8> FROM
9>   Employee
10> GO
1>
2> GRANT SELECT ON MyView(id) TO user1
3> GO
1>
2> drop view MyView
3> GO
1>
2>
3> drop table employee;
4> GO

The use of the CONTROL permission.

GRANT CONTROL ON DATABASE::sample TO peter

The use of the SELECT permission within the GRANT statement.

GRANT SELECT ON employee TO peter, mary

The use of the UPDATE permission within the GRANT statement.

GRANT UPDATE ON works_on (emp_no, enter_date) TO paul

The use of the VIEW DEFINITION permission.

GRANT VIEW DEFINITION ON OBJECT::employee TO peter
GRANT VIEW DEFINITION ON SCHEMA::dbo to peter

The use of the WITH GRANT OPTION clause of the GRANT statement.

GRANT SELECT ON works_on TO mary WITH GRANT OPTION

To grant permission is to give or allow permission to perform a type of operation on an object.

GRANT INSERT ON Product TO Paul
GRANT EXEC ON spDeletePurchaseEmployee TO Paul