Administering Database Permissions | Microsoft SQL Server 2000 Administrators Companion

3 4

Permissions are used to control access to database objects and to specify which users can perform certain database actions. You can set both server and database permissions. Server permissions are used to allow DBAs to perform database administration tasks. Database permissions are used to allow or disallow access to database objects and statements. In this section, we'll look at the types of permissions and how to allocate them.

Server Permissions

As just mentioned, server permissions are assigned to DBAs and allow them to perform administrative tasks. These permissions are defined on the fixed server roles. User logins can be assigned the fixed server roles, but these roles cannot be modified. (Server roles are explained in the section "Using Fixed Server Roles" later in this chapter.) Server permissions include SHUTDOWN, CREATE DATABASE, BACKUP DATABASE, and CHECKPOINT permissions. Server permissions are used only for authorizing DBAs to perform administrative tasks and do not need to be modified or granted to individual users.

Database Object Permissions

Database object permissions are a class of permissions that are granted to allow access to database objects. Object permissions are necessary to access a table or view by using SQL statements such as SELECT, INSERT, UPDATE, and DELETE. An object permission is also needed to use the EXECUTE statement to run a stored procedure. You can use Enterprise Manager or T-SQL commands to assign object permissions.

Using Enterprise Manager to Assign Object Permissions

To use Enterprise Manager to grant database object permissions to a user, follow these steps:

Expand a server group, expand a server, expand the database you want to assign permissions for, and click the Users folder. The users are then listed in the right pane. Right-click a user name, and choose Properties from the shortcut menu to display the Database User Properties window, shown in Figure 34-14.

Figure 34-14. The Database User Properties window.
Click the Permissions button to display the Database User Properties window, shown in Figure 34-15. (To display this tab, you can also right-click the user name, point to All Tasks in the shortcut menu, and then choose Manage Permissions.) On this tab, you manage the permissions assigned to this user. To assign permissions to this user to access objects within the database, select the appropriate check boxes in the SELECT, INSERT, UPDATE, DELETE, EXEC, and DRI columns of the list box. ("DRI" stands for "declarative referential integrity.") The objects are listed in the Object column. You can use the option buttons at the top of the window to view all objects or just those that this user already has permissions to access.

Figure 34-15. The Permissions tab of the Database User Properties window.

Using T-SQL to Assign Object Permissions

To use T-SQL to assign object permissions to a user, you run the GRANT statement. The GRANT statement has the following syntax:

 GRANT { ALL | permission } [ column ON {table | view} ] | [ ON table(column) ] | [ ON view(column) ] | [ ON { stored_procedure | extended_procedure } ] TO security_account [ WITH GRANT OPTION ] [ AS { group | role } ]

The security_account parameter must be one of the following account types:

SQL Server user
SQL Server role
Windows NT or Windows 2000 user
Windows NT or Windows 2000 group

Using the GRANT OPTION keyword allows the user or users specified in the statement to grant the specified permission to other users. This can be useful when you grant permissions to other DBAs. However, the GRANT option should be used with care.

The AS option specifies whose authority the GRANT statement is run under. To run the GRANT statement, a user or role must have been specifically granted authority to do so.

Here is an example of using the GRANT statement:

 GRANT SELECT, INSERT, UPDATE ON Customers TO MaryW WITH GRANT OPTION AS Accounting

The AS Accounting option is used because the Accounting role has permissions to grant permissions on the Customers table. The GRANT OPTION keyword allows MaryW to grant these permissions to other users.

MORE INFO
To view a list of permissions that can be specified in the GRANT statement, look up "GRANT, described (GRANT)" in the Books Online index.

Using T-SQL to Revoke Object Permissions

You can use the T-SQL REVOKE command to revoke a user's object permissions. The REVOKE statement has the following syntax:

 REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | permission } [ column ON {table | view} ] | [ ON table(column) ] | [ ON view(column) ] | [ ON { stored_procedure | extended_procedure } ] { TO | FROM } security_account [ CASCADE ] [ AS { group | role } ]

The security_account parameter must be one of the following account types:

SQL Server user
SQL Server role
Windows NT or Windows 2000 user
Windows NT or Windows 2000 group

The GRANT OPTION FOR option allows you to revoke permissions you previously granted by using the GRANT OPTION keyword, as well as revoke the permission. The AS option specifies whose authority the REVOKE statement is run under.

Here is an example of using the REVOKE statement:

 REVOKE ALL ON Customers FROM MaryW

The REVOKE ALL statement will remove all permissions the user MaryW has on the Customers table.

MORE INFO
To view a list of permissions that can be specified in the REVOKE statement, look up "REVOKE, described (REVOKE)" in the Books Online index.

Database Statement Permissions

In addition to assigning database object permissions, you can assign statement permissions. Object permissions enable users to access existing objects within the database, whereas statement permissions authorize them to create database objects, including databases and tables. The statement permissions are listed here:

BACKUP DATABASE Allows the user to execute the BACKUP DATABASE command
BACKUP LOG Allows the user to execute the BACKUP LOG command
CREATE DATABASE Allows the user to create new databases
CREATE DEFAULT Allows the user to create default values that can be bound to columns
CREATE PROCEDURE Allows the user to create stored procedures
CREATE RULE Allows the user to create rules
CREATE TABLE Allows the user to create new tables
CREATE VIEW Allows the user to create new views

You can assign statement permissions by using either Enterprise Manager or T-SQL.

Using Enterprise Manager to Assign Statement Permissions

To use Enterprise Manager to grant database statement permissions to a user, follow these steps:

Expand a server group, expand a server, and then expand the Databases folder. Right-click the name of the database you want to assign permissions for, and choose Properties from the shortcut menu to display the database's Properties window, shown in Figure 34-16.

Figure 34-16. A database's Properties window.
Click the Permissions tab, shown in Figure 34-17. Here you can assign statement permissions to the users and roles that have access to this database. The columns containing check boxes define the statement permissions that can be assigned, and the User/Role column lists the users and roles that have access to this database.

Figure 34-17. The Permissions tab of a database's Properties window.

Using T-SQL to Assign Statement Permissions

To assign statement permissions to a user by using T-SQL, you use the GRANT statement. The GRANT statement has the following syntax:

 GRANT { ALL | statement } TO   security_account

The statement permissions that can be assigned to a user are CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, BACKUP DATABASE, and BACKUP LOG, as described earlier. For example, to add the CREATE DATABASE and CREATE TABLE statement permissions to the user account JackR, use the following command:

 GRANT CREATE DATABASE, CREATE TABLE TO 'JackR'

As you can see, adding statement permissions to a user account is not a complex process.

Using T-SQL to Revoke Statement Permissions

You can use the T-SQL statement REVOKE to remove statement permissions from a user account. The REVOKE statement has the following syntax:

 REVOKE { ALL | statement } FROM   security_account

For example, to remove just the CREATE DATABASE statement permissions from the user account JackR, use the following command:

 REVOKE CREATE DATABASE FROM 'JackR'

As you can see, removing statement permissions from a user account is also not a complex process.