Tip #29 – Modifying Users and Groups with SQL

Database users can be modified using the sp_ModifyUserProperty system procedure. This procedure allows a developer to modify the following properties:

• Comment
• User Password
• Enable Internet
• Logins Disabled
• User Defined Property

In general you need administrative permissions to modify these properties with the exception of User Password. All users have rights to modify their own password. Administrators can modify the password for any user with the exception of the adssys account. You must be logged in as adssys to modify the adssys password.

Groups can be modified using the sp_ModifyGroupProperty system procedure. Unlike the sp_ModifyUserProperty groups only contain a comment property. Adding and removing users from groups is done with the sp_AddUserToGroup and sp_RemoveUserFromGroup system procedures. Examples of these are listed below:

EXECUTE PROCEDURE sp_ModifyUserProperty( 'chrisf', 'ENABLE_INTERNET', 'TRUE');

EXECUTE PROCEDURE sp_ModifyGroupProperty( 'Managers', 'COMMENT', 'Company managers group');

EXECUTE PROCEDURE sp_AddUserToGroup( 'chrisf', 'Managers');

EXECUTE PROCEDURE sp_RemoveUserFromGroup( 'roberts', 'Managers');