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, go to:

  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 identification number of the user, 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 that have been 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:

suser-id-function-in-sql.jpg

SQL SUSER_NAME
Function

This SQL security function returns the login identification name of the user, in other words this function 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:

suser-name-function-in-sql.jpg

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 login name of the user, login is sysname, which is optional, can be a SQL Server login Microsoft Windows user or group.
param2 It specifies whether the login name is validated and param 2 is of type int and is optional.

Example

An example image of the function is:

suser-sid-function-in-sql.jpg

SQL SUSER_SNAME Function

This SQL security function returns the login name associated with a security identification number and this function can be used as 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:

suser-sname-function-in-sql.jpg

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 TABLE 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


system-user-function-in-sql.jpg