Removing Logins and Users

The process of deactivating security accounts (for example, when an employee leaves a company) is similar to the process of adding a new user. Update the security mechanism in Microsoft® Windows NT® 4.0 or Windows® 2000 by first removing the user's Windows NT 4.0 or Windows 2000 user account. If the user has a Microsoft SQL Server™ user account, removed it from SQL Server along with any SQL Server database roles specifically defined for that user. Finally, remove any SQL Server login.

Removing a SQL Server user or Windows NT 4.0 or Windows 2000 user or group from a SQL Server database automatically removes the permissions defined for the user or group and prevents that user from using the database under the old security account. The permissions do not have to be removed separately. However, it is not possible to remove a user from SQL Server if that user currently owns objects (tables, procedures, or views) within a database. If the user owns objects, then either drop those objects before removing the user or transfer ownership to another existing user by using the sp_changeobjectowner system stored procedure.

Removing a user does not remove a login automatically, so it does not prevent the user from connecting to an instance of SQL Server. After being removed, the user can log in to the databases only through the guest account and perform activities under those permissions. To prevent a user from connecting to an instance of SQL Server, remove his or her login.

If a linked server login is set up but is no longer required, remove it to prevent unauthorized access to the linked server and to keep the security system as simple as possible.

To remove a user or group from a database

Transact-SQL