SQL Security Functions: Part 3

Introduction

In this article I describe the Transact-SQL security functions SUSER_ID, SUSER_NAME, SESSION_USER and SESSIONPROPERTY. The function given below returns information that is useful in managing security. To learn some other Transact-SQL security functions, go to:

1. SQL Security Functions: Part 1
   
2. SQL Security Functions: Part 2
   
   

SQL SUSER_ID Function

This SQL security SUSER_ID Function returns the login identification number of the user. And this function returns the value as an int. 

Syntax

SUSER_ID ( [ 'login' ] )

Arguments in SUSER_ID function

The argument of the function is:
 

Parameter	Description
login	It specifies the login name of the user. If it contains NULL then NULL will be returned.

Example

An example image of the function is:

 

SQL SUSER_NAME Function

This SQL security function returns information about whether the user has access to the specified database.

Syntax

SUSER_NAME ( [ server_user_id ] )

Arguments in the SUSER_NAME function

The argument of the function is:
 

Parameter	Description
server_user_id	It specifies the login identification number of the user. If it contains NULL then NULL will be returned.

Example

An example image of the function is:
 

SQL SESSION_USER Function

The SQL security SESSION_USER function will return the user name of the current context in the current database. And this function returns the value as an nvarchar. 

Syntax

SESSION_USER

Example

DECLARE @user_session varchar(50);

SET @user_session = SESSION_USER;

SELECT 'Current user session''s is: '+ @user_session;

GO

Output

SQL SESSIONPROPERTY Function

The SQL security SESSIONPROPERTY function returns the SET settings options of a session. And this function returns the value as a sql_variant. 

Syntax

SESSIONPROPERTY (arguments)

Arguments in SESSIONPROPERTY function

The arguments of the function is:

Parameter	Description
ANSI_NULLS	It specifies of the ISO behavior of not equal and equal to against null values is applied.
ANSI_PADDING	It specifies the control of the way the column stores values shorter than the defined size of the column. It is 1 for ON and 0 for OFF.
ANSI_WARNINGS	It specifies of the ISO behavior of warning messages and conditions produced. It is 1 for ON and 0 for OFF.
ARITHABORT	It specifies whether a query is ended when an overflow occurs. It is 1 for ON and 0 for OFF.
CONCAT_NULL_YIELDS_ NULL	It specifies the control whether concatenation results in a null when a null value is encountered. It is 1 for ON and 0 for OFF.
NUMERIC_ROUNDABORT	It specifies whether an error message is produced when a loss of precision would occur. It is 1 for ON and 0 for OFF.
QUOTED_IDENTIFIER	It specifies ISO rules about how to use quotation marks to delimit identifiers. It is 1 for ON and 0 for OFF.
<Any other string>	It specifies NULL meanng that the input is not valid.

Example

An example image of the function is: