Presents your 
SQL SERVER E-NEWSLETTER for April 29, 2003

<------------------------------------------->

UNDERSTAND STATEMENT PERMISSIONS

Statement permissions allow a user, role, or group specific permission
to execute a particular command, such as creating databases or objects.
For example, developers are often granted the CREATE TABLE, CREATE VIEW,
and CREATE FUNCTION permissions during database development.

It's a good idea to limit these statement permissions as much as
possible, because the creator of an object becomes the default owner, and that
can lead to conflicting permission situations later due to associated
ownership rights.

You can grant statement permissions individually or in a group of
several at a single time. There are a predetermined number of statement
permissions that you can grant, revoke, or deny as a group using the 
ALL argument.

Only the sysadmin role can grant statement permissions using the ALL
argument. The ALL argument can include CREATE DATABASE, CREATE DEFAULT,
CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE 
VIEW, BACKUP DATABASE, and BACKUPLOG.

Here is the syntax for granting permission:

GRANT {ALL | statement}
TO security_account

In the following example, the sysadmin grants the entire SQL Server
Developer role permission to the entire command list.

GRANT ALL
TO Developers

When granting statement permissions to a Windows NT group or global
group, list the computer or domain name followed by a backslash and then the
group name. In the following example, we'll grant Mary and John, as well
as the Corporate\BobJ Windows NT account, permission to the CREATE
DATABASE statement:

GRANT CREATE DATABASE
TO Mary, John [Corporate\BobJ]

You can also revoke and deny permission statements. The syntax is
similar to that of the GRANT statement.

In this example, we'll revoke all permissions we granted earlier to the
SQL Server Developer role:

REVOKE ALL
TO Developers

In this example, we'll deny the earlier permission granted to Mary for
the CREATE DATABASE statement:

DENY CREATE DATABASE
TO Mary

----------------------------------------