Presents your 
SQL SERVER E-NEWSLETTER for March 25, 2003

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

IDENTIFY PERMISSIONS WITHIN A DATABASE WITH SP_HELPROTECT

The system-stored procedure, sp_helprotect, identifies permissions
within the current SQL Server database. The system-stored procedure 
accepts four optional parameters:


* @name: This is the database object name. The value for this parameter
may also be a statement permission.

* @username: This is the user account within the database. This may be a
standard SQL Server login or a Windows user or group login that has a
database user account.

* @grantorname: This is the name of the database user account that has
granted permissions for database objects.

* @permissionarea: This parameter may be one or both of the following
values: o (this is alpha o for object permissions) or s (this is for
statement permissions).


The valid statement value for the @name parameter may be one of the
following:

* CREATE DATABASE
* CREATE DEFAULT
* CREATE FUNCTION
* CREATE PROCEDURE
* CREATE RULE
* CREATE TABLE
* CREATE VIEW
* BACKUP DATABASE
* BACKUP LOG

If none of the parameters are used, sp_helprotect will return all
permissions granted or denied for all objects and statement permissions. For
better readability of the output from this procedure, you should view 
the results in grid format.

The following sample script shows how sp_helprotect is used and will be
run in the Northwind database:

USE Northwind
GO
EXEC sp_helprotect @name = 'Orders'
GO
EXEC sp_helprotect @permissionarea = 's'
GO
EXEC sp_helprotect @permissionarea = 'o'
GO
EXEC sp_helprotect @permissionarea = 's o'
GO

The first sp_helprotect statement will return permissions for the Orders
table. The other three system stored procedure statements will return
results based on the value of the @permissionarea parameter.

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