sp_helpuser

Transact-SQL Reference

Transact-SQL Reference

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'

See Also

sp_adduser

sp_dropuser

sp_helpgroup

sp_helprole

sp_helpsrvrole

sp_helpsrvrolemember

System Stored Procedures