sp_helpuser (Transact-SQL)

Article
02/28/2023

Applies to: SQL Server

Reports information about database-level principals in the current database.

Important

sp_helpuser does not return information about securables that were introduced in SQL Server 2005 (9.x). Use sys.database_principals instead.

Transact-SQL syntax conventions

Syntax

  
sp_helpuser [ [ @name_in_db = ] 'security_account' ]

Arguments

[ @name_in_db = ] 'security_account' Is the name of database user or database role in the current database. security_account must exist in the current database. security_account is sysname, with a default of NULL. If security_account is not specified, sp_helpuser returns information about all database principals.

Return Code Values

0 (success) or 1 (failure)

Result Sets

The following table shows the result set when neither a user account nor a SQL Server or Windows user is specified for security_account.

Column name	Data type	Description
UserName	sysname	Users in the current database.
RoleName	sysname	Roles to which UserName belongs.
LoginName	sysname	Login of UserName.
DefDBName	sysname	Default database of UserName.
DefSchemaName	sysname	Default schema of the database user.
UserID	smallint	ID of UserName in the current database.
SID	smallint	User security identification number (SID).

The following table shows the result set when no user account is specified and aliases exist in the current database.

Column name	Data type	Description
LoginName	sysname	Logins aliased to users in the current database.
UserNameAliasedTo	sysname	User name in the current database to which the login is aliased.

The following table shows the result set when a role is specified for security_account.

Column name	Data type	Description
Role_name	sysname	Name of the role in the current database.
Role_id	smallint	Role ID for the role in the current database.
Users_in_role	sysname	Member of the role in the current database.
Userid	smallint	User ID for the member of the role.

Remarks

To see information about membership of database roles, use sys.database_role_members. To see information about server role members, use sys.server_role_members, and to see information about server-level principals, use sys.server_principals.

Permissions

Requires membership in the public role.

Information returned is subject to restrictions on access to metadata. Entities on which the principal has no permission do not appear. For more information, see Metadata Visibility Configuration.

Examples

A. Listing all users

The following example lists all users in the current database.

EXEC sp_helpuser;

B. Listing information for a single user

The following example lists information about the user database owner (dbo).

EXEC sp_helpuser 'dbo';

C. Listing information for a database role

The following example lists information about the db_securityadmin fixed database role.

EXEC sp_helpuser 'db_securityadmin';