SQL Security Functions: Part 2

Introduction

In this article I describe the Transact-SQL security functions IS_MEMBER , IS_SRVROLEMEMBER , LOGINPROPERTY, ORGINAL_LOGIN  and PERMISSION. To learn some other Transact-SQL security functions, go to:

1. SQL Security Functions: Part 1

SQL IS_MEMBER Function

This SQL security function indicates the current user is a member of the specified Microsoft Windows group or SQL Server database role and this function returns:

• 0 (zero): If the user is amember of the user defined database role.
• 1 (one): If the user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.
• Null: Either the group or role is not valid.

Syntax

IS_MEMBER( {'group' | 'role'})

Arguments in IS_MEMBER function

The arguments of the function are:
 

Parameter	Description
group	Group is the name of the Windows group that is being checked.
role	Role is the name of the SQL Server role that is being checked.

Example

An example image of the function is:

The above example returns 1, in other words the logged user is an admin user of the SQL Server database.

Output



SQL IS_SRVROLEMEMBER Function

This SQL security function indicates whether a SQL Server login is a member of the specified fixed server role and this function can be useful, whether the current user can perform an activity requiring the sever role's permission and this function returns:

• 0 (zero): If the login is not a member of role.
• 1 (one): If the login is a member of the role.
• Null: If the role or login is not valid.

Syntax

IS_SRVROLEMEMBER ( {'role'[, 'login'])

Arguments in IS_SRVROLEMEMBER  function

The arguments of the function are:
 

Parameter	Description
role	Role is the name of the server role being checked and its valid values are: sysadmin dbcreator diskadmin processadmin serveradmin setupadmin securityadmin
login	It is optional name of login to check.

Example

An example of the function is:

IF IS_SRVROLEMEMBER ('sysadmin') = 1

   print 'Current user''s login is a member of the sysadmin role'

ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0

   print 'Current user''s login is NOT a member of the sysadmin role'

ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL

   print 'ERROR: Invalid server role specified'

Output


 

SQL LOGINPROPERTY Function

This SQL security function returns the information about the login policy setting.

Syntax
 

LOGINPROPERTY ( {'loginName', 'propertyName')

Arguments in the LOGINPROPERTY function

The arguments of the function are:
 

Parameter	Description
loginName	It is the name of SQL Server login for which the login property status will be returned.
propertyName	It is an expression that contains the property information to be returned for the login and its values are: BadPasswordCount: It returns the number of consecutive attempts to log in with an incorrect password. BadPasswordTime: It returns the time of the last attempt to log in with an incorrect password. DaysUntilExpiration: It returns the number of days until the password expires. DefaultDatabase: It returns the SQL Server login default database as stored in metadata or master if no database is specified. Returns NULL for non-SQL Server provisioned users; for example, Windows authenticated users. DefaultLanguage: It returns the login default language as stored in metadata. Returns NULL for non-SQL Server provisioned users, for example, Windows authenticated users. HistoryLength: It returns the number of passwords tracked for the login, using the password-policy enforcement mechanism. 0 if the password policy is not enforced. Resuming password policy enforcement restarts at 1. IsExpired: It returns information that will indicate whether the login has expired. IsLocked: It returns information that will indicate whether the login is locked. IsMustChange: It returns information that will indicate whether the login must change its password the next time it connects. LockoutTime: It returns the date when the SQL Server login was locked out because it had exceeded the permitted number of failed login attempts. PasswordHash: It returns the hash of the password. PasswordLastSetTime: It returns the date when the current password was set.

Example

An example image of the function is:



SQL ORGINAL_LOGIN Function

This SQL security function returns the name of the login that connected to the instance of SQL Server. You can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context switches and this function returns the sysname.

Syntax

ORGINAL_LOGIN()

Example

An example image of the function is:



SQL PERMISSION Function

This SQL security function returns a value containing a bitmap that indicates the statement, object, or column permission of the current user. This function basically is used to determine whether the current user has the necessary permission to execute a statement or to "grant" a permission on an object to another user.

Syntax

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

Arguments in PERMISSION  function

The arguments of the function are:
 

Parameter	Description
objectid	It is the ID of a securable and if the object id is not specified then the bitmap value contains statement permissions for the current user; otherwise, the bitmap contains permissions on the securable for the current user.
column	It is the optional name of a column for which permission information is being returned.

Example

An example of the function is:

The example given below determines whether the current user can execute the CREATE TABLE statement or not.

IF PERMISSIONS()&2=2

   CREATE TABLE test_table (col1 INT)

ELSE

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

 

 Output