SQL Security Functions: Part 1

Introduction

In this article I describe the Transact-SQL security functions CURRENT_USER, HAS_DBACCESS, HAS_PERMS_BY_NAME and HASHBYTES. The functions given below returns information that is useful for managing security.

SQL CURRENT_USER Function

This SQL security function returns the name of the current user and this function is equivalent to USER_NAME() or in other words this function returns the sysname.

Syntax

CURRENT_USER

Example

An example image of the function is:

 

SQL HAS_DBACCESS Function

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

Syntax

HAS_DBACCESS('DataBase_Name')

Argument of the HAS_DBACCESS function

The argument of the function is:
 

Parameter	Description
DataBase_Name	It specifies the name of the database the user wants to access the information for.

Example

An example image of the function is:

SQL HAS_PERMS_BY_NAME Function

This SQL security function evaluates the effective permission of the current user on a securable and this function returns an integer value 1 or 0 or null; value 1 represents true and value 0 represents false and null represents invalid function arguments.

Syntax

HAS_PERMS_BY_NAME ( securable , securable_class , permission [ , sub-securable ] [ , sub-securable_class ] )

Arguments of the HAS_PERMS_BY_NAME function

The arguments of the function are:
 

Parameter	Description
securable	It specifies it is the name of the securable of type sysname.
securable_class	It is the name of the class of securable of type nvarchar(60).
permission	It is the name of the permission to be evaluated.
sub-securable	optional, It is the name of the securable sub-entity.
sub-securable_class	optional, It is the class of the securable sub-entity.

Example

Does database principal "dbo" have any permission in the current database?

An example image of the function is:

SQL HASHBYTES Function

This SQL security function returns the MD2, md4,md5,sha, or sha1 hash of its input and this function returns a value in the from of varbinary (maximum 8000 bytes).

Syntax

HashBytes ( '<algorithm>', { @input | 'input' } )

algorithm may have MD2 | MD4 | MD5 | SHA |SHA1

Arguments of the HASHBYTES function

The arguments of the function are:
 

Parameter	Description
algorithm	It specifies the hashing algorithm to be used to hash the input.
@input	It specifies a variable containing the data to be hashed.
input	It specifies a string to be hashed.

Example

An example image of the function is: