sp_helpuser

Description

Reports information about a particular user, group, or alias, or about all users, in the current database. Also identifies objects and user-defined datatypes owned by a users.

Syntax

sp_helpuser [name_in_db [, display_object]]

Parameters

name_in_db

is null or name of a valid user in the current database.

display_object

lists all objects and user-defined datatypes owned by name_in_db in the current database. If name_in_db is null, the objects and user-defined datatypes owned by the caller are listed. The output for objects includes object_name, object_type, and create_date, sorted by object_type and object_name. The output for user-defined datatype includes user type name.

Examples

Example 1

Displays information about all users in the current database:

sp_helpuser
Users_name ID_in_db    Group_name  Login_name 
---------  --------    ----------  ---------- 
ann        4           hackers     ann 
dbo        1           public      sa 
guest      2           public      NULL 
judy       3           hackers     judy

Example 2

Displays information about the database owner (user name “dbo”):

sp_helpuser dbo
Users_name     ID_in_db    Group_name  Login_name
----------     --------    ----------  ---------- 
dbo            1           public      sa 
Users aliased to user. 
Login_name
------------------------------- 
andy
christa
howard
linda 

Example 3

Displays objects owned by the user bill:

sp_helpuser bill, display_object
Object_name                Object_type               Create_date
-----------                ------------              ---------proc_update_titles        stored procedures           Apr 28 2007 04:47PM
author                    user table                  Apr 27 2007 04:47PM
publisher                 user table                  Apr 27 2007 05:47PM
titles                    user table                  Apr 27 2007 06:47PM
vw_author_in_ca           view                        Apr 27 2007 05:47PM

Example 4

Displays objects owned by the database owner (DBO):

sp_helpuser 'dbo', display_object
Object_name                Object_type              Create_date
-----------                ------------              --------------
enter_key                  encryption key            Sep 7 2007 03:37PM
sysalternatives            system table              Jul 17 2007 09:25AM
sysattributes              system table              Jul 17 2007 09:25AM
syscolumns                 system table              Jul 17 2007 09:25AM
.....                      ......                    ...........
sysquerymetrics            view                      Jul 17 2007 09:25AM

Usage

Permissions

Any user can execute sp_helpuser. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_adduser, sp_dropuser, sp_helpgroup