To remove a user or group from a databaseWhen employees leave a company, the process of deactivating their security accounts is similar to the process of adding a new user. The security mechanism in Microsoft® Windows NT® should be updated first by removing the user’s Windows NT user account (if they have one). If the user has a Microsoft SQL Server™ user account, it should be removed from SQL Server along with any SQL Server database roles specifically defined for that user. Finally, any SQL Server login should be removed.
Removing a SQL Server user or Windows NT 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 does own 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 connection to 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 SQL Server, remove his or her login.
If a linked server login is set up and 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
To remove a SQL Server login
To revoke a Windows NT user or group login access from SQL Server
To remove a linked server login