sp_helpuser
Reports information about Microsoft® SQL Server™ users, Microsoft Windows NT® users, and database roles in the current database.
Syntax
sp_helpuser [ [ @name_in_db = ] 'security_account' ]
Arguments
[@name_in_db =] 'security_account'
Is the name of a SQL Server user, Windows NT 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, the system procedure reports on all users, Windows NT users, and roles in the current database. When specifying a Windows NT user, specify the name that the Windows NT user is known by in the database (added using sp_grantdbaccess).
Return Code Values
0 (success) or 1 (failure)
Result Sets
Neither a user account nor an SQL Server or Windows NT user is specified for security_account.
| Column name | Data type | Description |
|---|---|---|
| UserName | sysname | Users and Windows NT users in the current database. |
| GroupName | sysname | Roles to which UserName belongs. |
| LoginName | sysname | Login of UserName. |
| DefDBName | sysname | Default database of UserName. |
| UserID | smallint | ID of UserName in the current database. |
| SID | smallint | User's security identification number (SID). |
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 that the login is aliased to. |
A role is specified for security_account.
| Column name | Data type | Description |
|---|---|---|
| Group_name | sysname | Name of the role in the current database. |
| Group_id | smallint | Role ID for the role in the current database. |
| Users_in_group | sysname | Member of the role in the current database. |
| Userid | smallint | User ID for the member of the role. |
Remarks
Use sp_helpsrvrole and sp_helpsrvrolemember to return information about fixed server roles.
Executing sp_helpuser for a database role is equivalent to executing sp_helpgroup for that database role.
Permissions
Execute permissions default to the public role.
Examples
A. List all users
This example lists all users in the current database.
EXEC sp_helpuser
B. List information for a single user
This example lists information about the user dbo.
EXEC sp_helpuser 'dbo'
C. List information for a database role
This example lists information about the db_securityadmin fixed database role.
EXEC sp_helpuser 'db_securityadmin'