SQL Security Functions: Part 4

Introduction

In this article, I describe the Transact-SQL security functions SUSER_ID, SUSER_NAME, SUSER_SID, SUSER_SNAME, and SYSTEM_USER. To learn some other Transact-SQL security functions, please go through the following articles of this series-

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

SQL SUSER_ID Function

This SQL security function returns the user's identification number, or you can say it returns the server user's ID number from the syslogins table. SUSER_ID returns an identification number only for logins explicitly provisioned inside SQL Server.

Syntax

ISUSER_ID ('login')

Argument of the SUSER_ID function

The argument of the function is.

Parameter	Description
login	It specifies the login name of the user.

Example

An example image of the function is.

SQL SUSER_NAME Function

This SQL security function returns the login identification name of the user; in other words, it returns the user login name for the server_user_id specified in the input argument, and if no "server_user_id" is specified, then it returns the name of the current user.

Syntax

SUSER_NAME ([server_user_id])

Argument of the SUSER_NAME  function

The argument of the function is.

Parameter	Description
server_user_id	It is the login identification number of the user

Example

An example image of the function is.

SQL SUSER_SID Function

This SQL security function returns the security identification number for the specified login name.

Syntax

SUSER_SID(['login'][,param2])

Arguments of the SUSER_SID function

The argument of the function is.

Parameter	Description
login	It is the user's login name; a login is sysname, which is optional and can be a SQL Server login Microsoft Windows user or group.
param2	It specifies whether the login name is validated and param 2 is optional and is of type int.

Example

An example image of the function is.

SQL SUSER_SNAME Function

This SQL security function returns the login name associated with a security identification number; This function can be a default constraint in either ALTER TABLE or CREATE TABLE.

Syntax

SUSER_SNAME ([server_user_id])

Argument of the SUSER_SNAME  function

The argument of the function is.

Parameter	Description
server_user_id	It is the login security identification number of the user.

Example

An example image of the function is.

SQL SYSTEM_USER Function

This SQL security function allows a system-supplied value for the current login to be inserted into a table when no default value is specified. You can use the SYSTEM_USER function with DEFAULT constraints in the CREATE and ALTER TABLE statements. You can also use it as any standard function.

Syntax

SYSTEM_USER

Example

An example of the function is.

DECLARE @sys_usr char(30);
SET @sys_usr = SYSTEM_USER;
SELECT 'The current system user is: '+ @sys_usr;

GO

Output