PERMISSIONS (T-SQL)

Returns a value containing a bitmap that indicates the statement, object, or column permissions for the current user.

Syntax

PERMISSIONS([objectid [, 'column']])

Arguments

objectid

Is the ID of an object. If objectid is not specified, the bitmap value contains statement permissions for the current user; otherwise, the bitmap contains object permissions on the object ID for the current user. The object specified must be in the current database. Use the OBJECT_ID function with an object name to determine the objectid value.

'column'

Is the optional name of a column for which permission information is being returned. The column must be a valid column name in the table specified by objectid.

Return Types

int

Remarks

PERMISSIONS can be used to determine whether the current user has the necessary permissions to execute a statement or to GRANT a permission on an object to another user.

The permissions information returned is a 32-bit bitmap.

The lower 16 bits reflect permissions granted to the security account for the current user, as well as permissions applied to Microsoft® Windows NT® groups or Microsoft SQL Server™ roles of which the current user is a member. For example, a returned value of 66 (hex value 0x42), when no objectid is specified, indicates the current user has permissions to execute the CREATE TABLE (decimal value 2) and BACKUP DATABASE (decimal value 64) statement permissions.

The upper 16 bits reflect the permissions that the current user can GRANT to other users. The upper 16 bits are interpreted exactly as those for the lower 16 bits described in the following tables, except they are shifted to the left by 16 bits (multiplied by 65536). For example, 0x8 (decimal value 8) is the bit indicating INSERT permissions when an objectid is specified. Whereas 0x80000 (decimal value 524288) indicates the ability to GRANT INSERT permissions, because 524288 = 8 x 65536. Due to membership in roles, it is possible to not have a permission to execute a statement, but still be able to grant that permission to someone else.

The table shows the bits used for statement permissions (objectid is not specified).

The table shows the bits used for object permissions that are returned when only objectid is specified.

The table shows the bits used for column-level object permissions that are returned when both objectid and column are specified.

A NULL is returned if a specified parameter is NULL or invalid (for example, an objectid or column that does not exist). The bit values for permissions that do not apply (for example EXECUTE permissions, bit 0x20, for a table) are undefined.

Use the bitwise AND (&) operator to determine each bit set in the bitmap returned by the PERMISSIONS function.

The sp_helprotect system stored procedure can also be used to return a list of object permissions for a user in the current database.

Examples

A. Use PERMISSIONS function with statement permissions

This example determines whether the current user can execute the CREATE TABLE statement.

IF PERMISSIONS()&2=2

    CREATE TABLE test_table (col1 INT)

ELSE

    PRINT 'ERROR: The current user cannot create a table.'

B. Use PERMISSIONS function with object permissions

This example determines whether the current user can insert a row of data into the authors table.

IF PERMISSIONS(OBJECT_ID('authors'))&8=8

    PRINT 'The current user can insert data into authors.'

ELSE

    PRINT 'ERROR: The current user cannot insert data into authors.'

C. Use PERMISSIONS function with grantable permissions

This example determines whether the current user can grant the INSERT permission on the authors table to another user.

IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000

    PRINT 'INSERT on authors is grantable.'

ELSE

    PRINT 'You may not GRANT INSERT permissions on authors.'

See Also