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:



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:

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:



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:



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