PERMISSIONS function returns a bitmap that indicates the statement, object or column permissions for current user.
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.
Below table shows the bits used for statement permissions where objectid is not specified.
Bit(Decimal) | Bit(Hexa) | Statement Permission |
1 | 0x1 | CREATE DATABASE (master database
only) |
2 | 0x2 | CREATE TABLE |
4 | 0x4 | CREATE PROCEDURE |
8 | 0x8 | CREATE VIEW |
16 | 0x10 | CREATE RULE |
32 | 0x20 | CREATE DEFAULT |
64 | 0x40 | BACKUP DATABASE |
128 | 0x80 | BACKUP LOG |
256 | 0x100 | Reserved |
Below table shows the bits used for object permissions that are returned when only objectid is specified.
Bit(Decimal) | Bit(Hexa) | Statement Permission |
1 | 0x1 | SELECT ALL |
2 | 0x2 | UPDATE ALL |
4 | 0x4 | REFERENCES ALL |
8 | 0x8 | INSERT |
16 | 0x10 | DELETE |
32 | 0x20 | EXECUTE (procedures only) |
4096 | 0x1000 | SELECT ANY (at least one column) |
8192 | 0x2000 | UPDATE ANY |
16384 | 0x4000 | REFERENCES ANY |
Below table shows the bits used for column-level object permissions that are returned when both objectid and column are specified.
Bit(Decimal) | Bit(Hexa) | Statement Permission |
1 | 0x1 | SELECT |
2 | 0x2 | UPDATE |
4 | 0x4 | REFERENCES |
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.
Syntax of PERMISSIONS Function :
PERMISSIONS ( [ objectid [ ,'column' ] ] )
objectid is an id of an object.
column is a column name. It is an optional value. It must be a valid column name in the table specified by objectid.
Return type of PERMISSIONS function is an integer.
Examples of PERMISSIONS Function :
Example 1 : Use of PERMISSIONS function in select clause
SELECT PERMISSIONS(OBJECT_ID('Products'),'ProductName') & 0x1
Output
1
Above example displays 1 means current user has select permission for specified table column.