Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : How to Get the Date Part of a DATETIME Data Type
Built-in Functions
Home > Functions > SQL Server Security Functions
SQL Server Security Functions

Security functions return information about users and roles.  Security functions return information that is userful in managing security.

Function Description
CURRENT_USER Returns the name of the current user.  This function is equivalent to USER_NAME().

Syntax:

CURRENT_USER
sys.fn_builtin_permissions Returns a description of the built-in permissions hierarchy of the server.

Syntax:

sys.fn_builtin_permissions ( [ DEFAULT | NULL ] | <empty_string> | 
                             '<securable_class>' )
fn_my_permissions Returns a list of the permissions effectively granted to the principal on a securable.  A related function is HAS_PERMS_BY_NAME.

Syntax:

fn_my_permissions ( <securable>, '<securable_class>' )
HAS_PERMS_BY_NAME Evaluates the effective permission of the current user on a securable.  A related function is fn_my_permissions.

Syntax:

HAS_PERMS_BY_NAME ( <securable>, <securable_class> <permission>
                    [ , <sub-securable> ] [ , <sub-securatble_class> ] )
IS_MEMBER Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.

Syntax:

IS_MEMBER ( { '<group>' | '<role>' } )
IS_SRVROLEMEMBER Indicates whether a SQL Server login is a member of the specified fixed server role.

Syntax:

IS_SRVROLEMEMBER ( '<role>' [ , '<login>' ] )
PERMISSIONS Returns a value containing a bitmap that indicates the statement, object or column permissions of the current user.

Syntax:

PERMISSIONS ( [ <objectid> [ , '<column>' ] ] )
SCHEMA_ID Returns the schema ID associated with a schema name.

Syntax:

SCHEMA_ID ( [ <schema_name> ] )
SCHEMA_NAME Returns the schema name associated with a schema ID.

Syntax:

SCHEMA_NAME ( [ <schema_id> ] )

Function Description
SESSION_USER Returns the user name of the current context in the current database.

Syntax:

SESSION_USER
SETUSER Allows a member of the sysadmin fixed server role of db_owner fixed database role to impersonate another user.

Syntax:

SETUSER [ '<username>' [ WITH NORESET ] ]
SUSER_ID Returns the login identification number of the user.

Syntax:

SUSER_ID ( [ '<login>' ] )
SUSER_SID Returns the security identification number (SID) for the specified login name.

Syntax:

SUSER_SID ( [ '<login>' ] )
SUSER_SNAME Returns the login name associated with a security identification number (SID).

Syntax:

SUSER_SNAME ( [ <server_user_id> ] )
SYSTEM_USER Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.

Syntax:

SYSTEM_USER
SUSER_NAME Returns the login identification name of the user.

Syntax:

SUSER_NAME ( [ <server_user_id> ] )
USER_ID Returns the identification number for a database user.

Syntax:

USER_ID ( [ '<user>' ] )
USER_NAME Returns a database user name from a specified identification number.

Syntax:

USER_NAME ( [ '<id>' ] )

Related Articles :