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.

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