Introduction to grant Usage

The GRANT statement for SQL Server 2000 is used to grant users, roles, and groups permission to use database objects and to run certain stored procedures and functions.

Grant's syntax is very simple--although it is one of the most powerful statements in SQL Server 2000. The following example grants Pete (Pete) permission to INSERT, update, and delete the authors table in the pubs database. The WITH GRANT option means that Pete can also use these statements to authorize other users. Use pubs
Go

GRANT INSERT, UPDATE, DELETE
On authors
To Pete
With GRANT OPTION
Go

You can also grant Pete all permissions so that he can also use create DATABASE, create FUNCTION, create rule, create TABLE, BACKUP DATABASE, and other statements. However, we generally want to restrict user access and manipulate database permissions.

In addition to individual users, you can use grant statements for SQL Server roles, Windows NT users, and Windows NT groups. If there is an authorization conflict between a user and/or a group or role, then the most restrictive authorization is handled. You cannot authorize users, roles, or groups in other databases.

You can use the REVOKE statement to dismiss a permission that has been granted. You can also use the DENY statement to stop the user from the group (or role) from which he or she is subordinate to get permission to grant that group (or role).

In the following example, we will see who owns the Authors table permissions in the pubs database. Then we give Pete permission to access the database. Finally, we give him permission to insert, UPDATE, and delete the authors table.

First, we run Sp_helpprotect to see which people on the authors table have permissions.

Exec sp_helprotect Authors
Go

The results should resemble the following:

owner  object   grantee  grantor   protecttype  Action             Column
-----       ----- -      -------   -----------   -------------     ---------            -------
dbo       authors  guest    dbo            grant               Delete
dbo       authors  guest    dbo            grant               Insert
dbo       authors  guest    Dbo           grant               references    (all+new)
dbo        authors  guest    dbo           Grant               select              (all+new)
dbo       Authors   guest    dbo           Grant               update           (all+new)

Because before Pete gets access to the database, we can't grant him permission to execute any of the statements, so we should first let Pete have access to the database. (In fact, in order to be able to use the granted permission, Pete needs a SQL Server 2000 logon account and permission to access the database.) This example creates a permission to access a database for a user who does not already exist in SQL Server 2000. ）

EXEC sp_grantdbaccess Pete
Go

You will see "granted database access to ' Pete".

Let's run the grant INSERT, UPDATE, DELETE statement mentioned earlier. Now that the Sp_helpprotect is rerun, the last three lines should show Pete and the information he just received.