Dibaca dalam bahasa Inggeris Edit

Kongsi melalui

sp_helpuser (Transact-SQL)

  • Artikel

Applies to: SQL Server

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

Penting

sp_helpuser doesn't return information about securables that were introduced in SQL Server 2005 (9.x) and later versions. Use sys.database_principals instead.

Transact-SQL syntax conventions

Syntax

syntaxsql 
sp_helpuser [ [ @name_in_db = ] N'name_in_db' ]
[ ; ]

Arguments

[ @name_in_db = ] N'name_in_db'

The name of database user or database role in the current database. @name_in_db is sysname, with a default of NULL. @name_in_db must exist in the current database. If @name_in_db isn't specified, sp_helpuser returns information about all database principals.

Return code values

0 (success) or 1 (failure).

Result set

The following table shows the result set when no user account, SQL Server, or Windows user is specified for @name_in_db.

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 @name_in_db.

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 don't appear. For more information, see Metadata Visibility Configuration.

Examples

A. List all users

The following example lists all users in the current database.

SQL 
EXEC sp_helpuser;

B. List information for a single user

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

SQL 
EXEC sp_helpuser 'dbo';

C. List information for a database role

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

SQL 
EXEC sp_helpuser 'db_securityadmin';

Related content