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.
- 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
Comments
Join the conversation! Your thoughts help the community grow.