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.
sp_helpuser [name_in_db [, display_object]]
is null or name of a valid user in the current database.
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.
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
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
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
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
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.
Any user can execute sp_helpuser. Permission checks do not differ based on the granular permissions settings.
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 |
|
System procedures sp_adduser, sp_dropuser, sp_helpgroup
