Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » System FunctionsRSS Feeds

PERMISSIONS Function

Posted By: Sarita Patel     Category: SQL Server     Views: 2172

This article explains about PERMISSIONS function in sql server with examples.

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.
  
Share: 

 
 

Didn't find what you were looking for? Find more on PERMISSIONS Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of PERMISSIONS Function is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!