Presents your 
SQL SERVER E-NEWSLETTER for August 29, 2002

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

Learn how to use PERMISSIONS()

The PERMISSION() function can be used to identify three permissions for
the current user:

* Statement permissions within a database
* Object permissions
* Column level permissions

The function may have two values supplied. The first value is the object
identifier that may or may not be supplied. The second value is the
column value of the first value object identifier that must be supplied. If
neither value is supplied, it will return an integer used to determine the
user's statement permissions allowed in the database. This function is
available on SQL Server 7.0 and 2000.

The following example is a table from SQL Server books online to
identify the bit set value that correlates to the permission. Using bit value
manipulation from the returned integer of the function PERMISSIONS() can
determine the permission of the current user.

In the following list, the first value is the bit value in decimal
format along with its respective statement permission.

1 - CREATE DATABASE (master database only)
2 - CREATE TABLE
4 - CREATE PROCEDURE
8 - CREATE VIEW
16 - CREATE RULE
32 - CREATE DEFAULT
64 - BACKUP DATABASE
128 - BACKUP LOG
256 - Reserved

Below, the first value is the bit value in decimal format along with its
respective object permission. The objectid must be supplied in the function.

1 - SELECT ALL
2 - UPDATE ALL
4 - REFERENCES ALL
8 - INSERT
16 - DELETE
32 - EXECUTE (procedures only)
4096 - SELECT ANY (at least one column)
8192 - UPDATE ANY
16384 - REFERENCES ANY

In the following list, the first value is the bit value in decimal
format along with its object's column level permission. The objectid 
and column name must be supplied in the function.

1 - SELECT
2 - UPDATE
4 - REFERENCES

Below is a sample script to determine the user's permission to create a table.

IF PERMISSIONS()&2=2
BEGIN
    CREATE TABLE x_MyTable (counter INT IDENTITY PRIMARY KEY, code INT
NULL)
    INSERT x_MyTable (code) VALUES ( 999 )
    SELECT * FROM x_MyTable
    DROP TABLE x_MyTable
END

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