Presents your  
SQL SERVER E-NEWSLETTER for August 19, 2003 
 
<------------------------------------------->

USE GRANT FOR STREAMLINING USER PERMISSIONS   
  
SQL Server 2000's GRANT statement authorizes users, roles, and groups to

use database objects and gives permission to execute certain stored 
procedures and functions.   
  
Although GRANT is one of the most powerful statements in SQL Server 
2000, its syntax is pretty simple. The following example gives Pete
INSERT, UPDATE, and DELETE permissions on the authors table in the pubs
database. 
The WITH GRANT OPTION means that Pete can also grant other users 
permissions to these statements.   
  
USE pubs   
GO   
  
GRANT INSERT, UPDATE, DELETE   
ON authors   
TO Pete   
WITH GRANT OPTION   
GO   
  
You could also grant Pete ALL, giving him permissions to use CREATE 
DATABASE, CREATE FUNCTION, CREATE RULE, CREATE TABLE, BACKUP DATABASE,
and other statements. However, it's more likely that you'll want Pete to
have limited access to the database and to what he can do.   
  
In addition to using GRANT with an individual user, you also can use 
GRANT with a SQL Server role, a Windows NT user, or a Windows NT group.
If there are permission conflicts between a user and/or a group or role,
the most restrictive permissions prevail. Permissions cannot, however, be 
granted to a user, role, or group in another database.   
  
You can use a REVOKE statement to remove granted permissions. Or, you 
can use a DENY statement to stop a user from gaining permission through a 
group or role to which he or she belongs that has been granted permission. 
  
In the examples below, we'll see who has permission to what on the 
authors table in the pubs database. Then, we'll give Pete access to the 
database. Finally, we'll give him permission to the INSERT, UPDATE, and
DELETE statements on the authors table.   
  
First, let's run sp_helprotect to see who has permission to do what on 
the authors table.   
  
Exec sp_helprotect authors   
GO   
  
Your results should look similar to this:   
  
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)   
  
Since we can't give Pete permission to any statements until he has 
permission to the pubs database, we'll do that next. (In the real 
world, Pete would need a SQL Server 2000 login account and permission to the
database to utilize his newly granted permission. This example creates database 
access for a user that doesn't exist yet as far as SQL Server 2000 is 
concerned.)   
  
EXEC sp_grantdbaccess Pete   
GO   
  
You should get a statement such as "Granted database access to 'Pete'." 

  
Let's run the earlier GRANT, INSERT, UPDATE and DELETE statement. Now 
rerun sp_helprotect authors. The last three rows should show Pete and
his newly granted permissions.   
  
J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about 
technology for more than two decades.   
  
----------------------------------------