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 other Transact-SQL security functions, please go through the link below.

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 a member 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 the 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 SQL Server database admin user.

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 helpful 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 the 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 the 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 an optional name of the 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 the 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, such as 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 indicating whether the login must change its password the next time it connects. LockoutTime: It returned 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 returned 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 connected to the instance of the 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 current user's statement, object, or column permission. This function determines whether the current user has the necessary permission to execute a statement or "grant" permission on an object to another user.

Syntax

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

Arguments in the 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 below 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.';

Output 

Summary

This article is about SQL Security Functions in SQL Server. We learned here about Transact-SQL security functions IS_MEMBER, IS_SRVROLEMEMBER, LOGINPROPERTY, ORGINAL_LOGIN, and PERMISSION.

Reference

• SQL Security Functions: Part 3
• SQL Security Functions: Part 4