Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Security Functions

One set of security functions helps a database administrator get information about database users and roles. The other set helps us get trace information and is specific to SQL Server Profiler traces (don't ask why they're qualified as security functions because they're used for tracing).

Functions that provide information about users and roles are summarized in the following table:

Function

Parameters

Meaning/Example

HAS_DBACCESS

Database name

Returns 1 if the current user has access to the database; returns 0 if no access. NULL is returned if the specified database does not exist.

Example:

SELECT HAS_DBACCESS('pubs')

IS_MEMBER

Windows group or database role

Determines whether the current user is a member of a Windows group or database role.

Example:

SELECT IS_MEMBER('db_owner')

IS_SRVROLEMEMBER

SQL Server role and (optionally) login name

Determines whether the current user (or specified login) is a member of SQL Server role.

Example:

SELECT IS_SRVROLEMEMBER('sysadmin')

SUSER_SID

Optional parameter of login name

Returns the security identifier of the current user or login name.

Example:

SELECT SUSER_SID()

SUSER_SNAME

Optional parameter of security identifier

Returns the name of the current user or user associated with the security identifier specified.

Example:

SELECT SUSER_SNAME()

USER_ID

Optional parameter of user name

Returns the user identifier within the current database.

Example:

SELECT USER_ID()

USER

None

Returns database user name.

Example:

SELECT USER


  • + Share This
  • 🔖 Save To Your Account