sp_helpuser

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

sp_helpuser reports information about all users of the current database. If you specify a name_in_db, sp_helpuser reports information only on the specified user.

If the specified user is not listed in the current database’s sysusers table, sp_helpuser checks to see if the user is aliased to another user or is a group name.

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:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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