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 is 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 the 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 a 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 the SESSIONPROPERTY function

The arguments of the function are.

Parameter	Description
ANSI_NULLS	It specifies that the ISO behavior of not equal and equal to against null values is applied.
ANSI_PADDING	It specifies the control of how the column stores value shorter than the defined size of the column. It is 1 for ON and 0 for OFF.
ANSI_WARNINGS	It specifies 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 of 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 occurs. 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, meaning that the input is not valid.

Example

An example image of the function is.

Summary

This article is about SQL Security Functions in SQL Server. We learned here about Transact-SQL security functions SUSER_ID, SUSER_NAME, SESSION_USER, and SESSIONPROPERTY.

Reference

• SQL Security Functions: Part 4