sp_droprolemember (T-SQL)

Removes a security account from a Microsoft® SQL Server™ role in the current database.

Syntax

sp_droprolemember [@rolename =] 'role',
    [@membername =] 'security_account'

Arguments

'role'

Is the name of the role that the member is being removed from. role is sysname, with no default. role must exist in the current database.

'security_account'

Is the name of the security account being removed from the role. security_account is sysname, with no default. security_account can be a SQL Server user or another SQL Server role, or a Microsoft Windows NT® user or group. security_account must exist in the current database. When specifying a Windows NT user or group, specify the name that the Windows NT user or group is known by in the database (added using sp_grantdbaccess).

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_droprolemember removes a role member by deleting a row from the sysmembers table. When removing a member from a role, the permissions applied to the role are no longer applied to the former member of the role.

sp_droprolemember cannot be used to remove a Windows NT user from a Windows NT group; this must be done in the Windows NT security system. To remove a user from a fixed server role, use sp_dropsrvrolemember. Users cannot be removed from the public role, and dbo cannot be removed from any role.

Use sp_helpuser to see the members of a SQL Server role, and use sp_addrolemember to add a member to a role.

sp_droprolemember cannot be executed from within a user-defined transaction.

Permissions

Only members of the db_owner or db_securityadmin fixed database roles can execute sp_droprolemember. Only a member of the db_owner fixed database role can remove users from a fixed database role.

Examples

This example removes the user JonB from the role Sales.

EXEC sp_droprolemember 'Sales', 'Jonb'

See Also