| title | sp_helpuser (Transact-SQL) | ||
|---|---|---|---|
| description | sp_helpuser (Transact-SQL) | ||
| author | markingmyname | ||
| ms.author | maghan | ||
| ms.date | 03/14/2017 | ||
| ms.service | sql | ||
| ms.subservice | system-objects | ||
| ms.topic | reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| dev_langs |
|
[!INCLUDE 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 [!INCLUDEssVersion2005]. Use sys.database_principals instead.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_helpuser [ [ @name_in_db = ] 'security_account' ]
[ @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.
0 (success) or 1 (failure)
The following table shows the result set when neither a user account nor a [!INCLUDEssNoVersion] 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. |
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.
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.
The following example lists all users in the current database.
EXEC sp_helpuser;
The following example lists information about the user database owner (dbo).
EXEC sp_helpuser 'dbo';
The following example lists information about the db_securityadmin fixed database role.
EXEC sp_helpuser 'db_securityadmin';
Security Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)
Principals (Database Engine)
sys.database_principals (Transact-SQL)
sys.database_role_members (Transact-SQL)
sys.server_principals (Transact-SQL)
sys.server_role_members (Transact-SQL)