Managing Access to Tables and Columns

Tables and columns store the data that applications retrieve and create. Access to this data is managed by the SQL Server 2005 permission hierarchy. You can manage this permission hierarchy with the GRANT, DENY, and REVOKE statements.

GRANT Allows a user or role to perform the operation specified by the granted permission.
DENY Denies a user or role the specified permission and prevents them from inheriting this permission.from other roles.
REVOKE Drops a previously denied or granted permission.

Modifying Access to a Table

Access to a table is controlled by the effective permissions that the user has on the table. You can control user access to tables by managing permissions on tables. The permissions that you can manage on tables are specified on Table 2-4. You can assign these permissions to database users and roles.

Table 2-4. Table Permissions

Permission	Description
ALTER	Can modify table properties
CONTROL	Provides ownership-like permissions
DELETE	Can delete rows from the table
INSERT	Can insert rows in the table
REFERENCES	Can reference the table from a foreign key
SELECT	Can select rows from the table
TAKE OWNERSHIP	Can take ownership of the table
UPDATE	Can update rows on the table
VIEW DEFINITION	Can access the the table's metadata

Granting Access to a Table

You can provide access to database users and roles by using the GRANT statement. The following sample grants SELECT, INSERT, and UPDATE permissions to Sara on the Sales.Customer table. (The code in this and subsequent sections for managing access to tables is included in the sample files as ManagingAccessToTables.sql.)

-- Change the connection context to the database AdventureWorks.
USE AdventureWorks;
GO
-- Grant some permissions to Sara on the Sales.Customer table.
GRANT SELECT,INSERT,UPDATE
ON Sales.Customer
TO Sara;

Limiting Access to a Table

When you need to prevent a user from accessing a table, you might face one of two scenarios. If you have granted permission to the user on the table before, you should use the REVOKE statement to clear the previously granted permissions. For example:

-- Change the connection context to the database AdventureWorks.
USE AdventureWorks;
GO
-- Revoke SELECT permissions from Sara on the Sales.Customer table
REVOKE SELECT
ON Sales.Customer
TO Sara;

However, the user might maintain the revoked permission by belonging to a role with this permission granted. In that case, you need to use the DENY statement in order to deny access for that user. For example:

-- Change the connection context to the database AdventureWorks.
USE AdventureWorks;
GO
-- Deny DELETE permission to Sara on the Sales.Customer table,
-- regardless of what permissions this user might
-- inherit from roles.
DENY DELETE
ON Sales.Customer
TO Sara;

Providing Access to Columns Individually

SQL Server 2005 provides you the option to grant or refuse access to columns individually instead of working with tables. This feature gives you the flexibility to refuse access, for example, to confidential data from some columns. The permissions that you can manage on table columns are described in Table 2-5.

Table 2-5. Column Permissions

Permission	Description
SELECT	Can select the column
UPDATE	Can update the column
REFERENCE	Can reference the column from a foreign key

Granting Access to Columns

You can grant access to columns individually by using the GRANT option. The following sample grants SELECT and UPDATE permissions to Sara on the Demographics and ModifiedDate columns for the Sales.Individual table. (The code in this and subsequent sections for managing access to columns is included in the sample files as ManagingAccessToColumns. sql.)

-- Change the connection context to the database AdventureWorks.
USE AdventureWorks;
GO
-- Grant SELECT and UPDATE permissions to Sara
-- on some specific columns of the Sales.Individual table
GRANT SELECT,UPDATE (
Demographics,
ModifiedDate)
ON Sales.Individual
TO Sara;

Revoking Access to Columns

Similarly, you can revoke access to columns individually using the REVOKE statement. Remember that if you want to prevent a user from gaining permission, you need to use the DENY statement.

-- Change the connection context to the database AdventureWorks.
USE AdventureWorks;
GO
-- Revoke previosly granted or denied permissions
-- from Sara on the Demographics column.
REVOKE UPDATE (Demographics)
ON Sales.Individual
TO Sara;