Security Functions

---

Security Functions in SQL Server

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

All the functions are listed with their description as follows:

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

Syntax:

CURRENT_USER

Function	Description
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>' )

Function	Description
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>' )

Function	Description
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> ] )

Function	Description
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>' } )

Function	Description
IS_SRVROLEMEMBER	Indicates whether a SQL Server login is a member of the specified fixed server role.

Syntax:

IS_SRVROLEMEMBER ( '<role>' [ , '<login>' ] )

Function	Description
PERMISSIONS	Returns a value containing a bitmap that indicates the statement, object or column permissions of the current user.

Syntax:

PERMISSIONS ( [ <objectid> [ , '<column>' ] ] )

Function	Description
SCHEMA_ID	Returns the schema ID associated with a schema name.

Syntax:

SCHEMA_ID ( [ <schema_name> ] )

Function	Description
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

Function	Description
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 ] ]

Function	Description
SUSER_ID	Returns the login identification number of the user.

Syntax:

SUSER_ID ( [ '<login>' ] )

Function	Description
SUSER_SID	Returns the security identification number (SID) for the specified login name.

Syntax:

SUSER_SID ( [ '<login>' ] )

Function	Description
SUSER_SNAME	Returns the login name associated with a security identification number (SID).

Syntax:

SUSER_SNAME ( [ <server_user_id> ] )

Function	Description
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