Home > SQL Server, T-SQL > PERMISSIONS Function – SQL Server

PERMISSIONS Function – SQL Server


PERMISSIONS() : This function is used to determine whether the current user has the permissions required to execute a statement or to GRANT a permission to another user. This permission function is used to get the current user permissions for statements, objects, or columns in a current database.

It returns an integer value (32 bit). But, we have to extract the bit value by applying bit-wise operator to identify the specific permission. The lower 16 bits contains permissions granted to the user and the upper 16 bits contains the permissions that the user can GRANT to other users

There are three ways to use this function

  1. Using this function without argument yields statement permissions of the current user
  2. Using only object id argument yields object permissions of the current user. This will also get us the permission for granting ability for the user using upper bit value of the 32 bit integer output
  3. Using both object id and column name yields column level permission of current user

Function Syntax

PERMISSIONS ( [ objectid [ , ‘column’ ] ] )

Bit calculation to get current user permission:

1. Bit calculation for the function output that has no arguments

pic12. Bit calculation for the function output that has object id only argument

pic13. Bit calculation for the function output the has both object id and column name

pic2Experiment

1. user permission() function without argument and fetching the statement permission

pic3Note: This function will be removed from the future version of SQL Server. So, Avoid using this in the new development.

Use these  fn_my_permissions and Has_Perms_By_Name functions to get the permission information.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: