Professional SQL Server™ 2005 Programming

A.8. Security Functions

The security functions return information about users and roles. They are:

• HAS_DBACCESS

• IS_MEMBER

• IS_SRVROLEMEMBER

• SUSER_ID

• SUSER_NAME

• SUSER_SID

• USER

• USER_ID

A.8.1. HAS_DBACCESS

The HAS_DBACCESS function is used to determine whether the user that is logged in has access to the database being used. A return value of 1 means the user does have access, and a return value of 0 means that he or she does not. A NULL return value means the database_name supplied was invalid. The syntax is as follows:

HAS_DBACCESS ('<database_name>')

A.8.2. IS_MEMBER

The IS_MEMBER function returns whether the current user is a member of the specified Windows NT group/SQL Server role. The syntax is as follows:

IS_MEMBER ({'<group>' | '<role>'})

The group parameter specifies the name of the NT group and must be in the form domain\group. The role parameter specifies the name of the SQL Server role. The role can be a database fixed role or a user-defined role but cannot be a server role.

This function will return a 1 if the current user is a member of the specified group or role, a 0 if the current user is not a member of the specified group or role, and NULL if the specified group or role is invalid.

A.8.3. IS_SRVROLEMEMBER

The IS_SRVROLEMEMBER function returns whether a user is a member of the specified server role. The syntax is as follows:

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

The optional login parameter is the name of the login account to check — the default is the current user. ...