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 are given below return information that is useful for managing security.

SQL CURRENT_USER Function

This SQL security function returns the current user's name and is equivalent to USER_NAME() or, in other words, 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.

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 returns an integer value 1 or 0 or null; value 1 represents true, 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 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.

Summary

This article is about SQL Security Functions in SQL Server. we learned here about the Transact-SQL security functions CURRENT_USER, HAS_DBACCESS, HAS_PERMS_BY_NAME, and HASHBYTES. 

Reference

• SQL Security Functions: Part 2
• SQL Security Functions: Part 3
• SQL Security Functions: Part 4