User Access to Objects

Permissions for principals to access objects are assigned using GRANT, DENY and REVOKE.

This article discusses permissions on:

1. Table and View
2. Stored procedures
3. Schema

Table and View permissions

Permissions are set for the principal to SELECT, INSERT, UPDATE and DELETE.

Grant – Allows the permissions

GRANT SELECT ON OBJECT::Marketing.Salesperson
TO Peter;
GO

Deny – Denies the permissions

DENY SELECT ON OBJECT::Marketing.Salesperson
(Firstname, Address, Phone) -- (this is optional, and shows how column-level security can be applied)
TO Peter;
GO

Revoke – Removes the last permission set (removes either Grant of Deny)

REVOKE SELECT ON OBJECT::Marketing.Salesperson
TO Peter;
GO

Stored procedures permissions

Permissions are set for the principal to EXECUTE, ALTER, VIEW DEFINITION
Grant – Allows the permissions

GRANT EXECUTE ON Reports.GetProductColours --(here we have not used the OBJECT:: ref. It’s optional)
TO Peter;
GO

Deny – Denies the permissions

DENY ALTER ON OBJECT:: Reports.GetProductColours
(Firstname, Address, Phone) -- (this is optional, and shows how column-level security can be applied)
TO Peter;
GO

Revoke – Removes the last permission set (removes either Grant of Deny)

REVOKE VIEW DEFINITION ON OBJECT:: Reports.GetProductColours
TO Peter;
GO

Schema permissions

A schema is really just a container for objects. In the above examples we see a Marketing schema and a Reports schema. They are helpful to organise a database. They are created with the CREATE SCHEMA statement, and formally referred to by a name of the form: Server.Database.Schema.Object.
'dbo' is a schema. 'sys' is another.

Each user (apart from Windows group logins) is assigned a default schema, used when a user refers to an object without specifying the schema name. Consider this:

SELECT ProductID, Name, Price FROM Products

There could be a Products table in more than one schema. What will happen here is SQL Server will look first in the user’s default schema for a Products table, after that it will look in the dbo schema.
It would be better to write the two-part name:

SELECT ProductID, Name, Price FROM Production.Products

GRANT, DENY, REVOKE are all still used for schemas, but as a schema can contain tables, views, stored procedures, functions etc, you can set the permissions mentioned above for all these:

GRANT SELECT ON SCHEMA:: Production
TO Peter;
GO
DENY EXECUTE ON SCHEMA:: Production
TO Peter;
GO