A Database Owner or a System Administrator can use sp_dropuser to deny an Adaptive Server user access to the database in which sp_dropuser is executed. (If a “guest” user is defined in that database, the user can still access that database as “guest.”)
The following is the syntax, where name_in_db is usually the login name, unless another name has been assigned:
sp_dropuser name_in_db
You cannot drop a user who owns objects. Since there is no command to transfer ownership of objects, you must drop objects owned by a user before you drop the user with sp_dropuser. To deny access to a user who owns objects, use sp_locklogin to lock his or her account.
You also cannot drop a user who has granted permissions to other users. Use revoke with cascade to revoke permissions from all users who were granted permissions by the user to be dropped, then drop the user. You must then grant permissions to the users again, if appropriate.
