如何删除SQL Server登录名及其所有依赖项

介绍 (Introduction)

Sometimes, we need to drop a SQL Server Login because we gave an access to a person who left, this login was used by an application that has been decommissioned for example.

有时，我们需要删除SQL Server登录名，因为我们授予了一个离开的人访问权限，例如，该登录名已由已停用的应用程序使用。

To do so, we can use SQL Server Management Studio (SSMS) as follows:

为此，我们可以如下使用SQL Server Management Studio（SSMS）：

1. Open SSMS
   打开SSMS
2. Connect to a SQL Server instance
   连接到SQL Server实例

3. 

4. 

5. 

6. Click on “OK”
   点击“确定”

We could also execute a DROP LOGIN statement:

我们还可以执行DROP LOGIN语句：

 
DROP LOGIN login_name ;
 

No matter the path you followed, if everything went OK, your login is now dropped. But, as SSMS stated, you may have left a few things inside your databases. If there were mappings between this login and one or more database users (one per database), these users are still there.

无论您走的是什么路径，如果一切顺利，现在将删除您的登录名。 但是，正如SSMS所说，您可能在数据库中留下了一些东西。 如果此登录名与一个或多个数据库用户（每个数据库一个）之间存在映射，则这些用户仍然存在。

We can come to that situation too when we restored one or more databases from a SQL Server instance (source server) to another server (destination server) where all logins mapped to these databases are not created on a destination server, or they are created but with different SID. Anyway, if you came to the situation you expected then it’s ok, but if not, it’s mandatory to carefully consider the advice in that warning that says we need to transfer ownership of schemas to another user.

当我们将一个或多个数据库从SQL Server实例（源服务器）还原到另一台服务器（目标服务器）时，我们也遇到这种情况，其中映射到这些数据库的所有登录名都没有在目标服务器上创建，或者创建了，但是创建了具有不同的SID。 无论如何，如果您遇到了您所期望的情况，那是可以的，但是如果没有，则必须仔细考虑警告中的建议，该建议表明我们需要将架构所有权转让给另一位用户。

But there are also cases where these steps will fail. For instance, if the login you want to drop is set as a database owner for one or more databases. It that case, you’ll get following screens in SSMS:

但是在某些情况下，这些步骤也会失败。 例如，如果您要删除的登录名被设置为一个或多个数据库的数据库所有者。 在这种情况下，您将在SSMS中获得以下屏幕：

If you used T-SQL, you’ll get following error message:

如果您使用的是T-SQL，则会收到以下错误消息：

In this article, we’ll try to drop a SQL Server Login and all its dependencies using T-SQL statements. This test and its conclusions will provide us relevant information that we can use if we are willing to automate this task or at least some parts of it.

在本文中，我们将尝试使用T-SQL语句删除SQL Server登录名及其所有依赖项。 该测试及其结论将为我们提供相关信息，如果我们愿意自动执行此任务或其至少某些部分，可以使用这些信息。

In the following sections, we will first define and build a test case. Our objective will be to drop a SQL Server Login [ApplicationSQLUser1] and its corresponding databases users, both called [UserA]. We’ll first start trying to remove [UserA] database user from [Db1] database. Once we managed to perform this task, we’ll review the steps we followed and consider their appropriate sequencing. Once this is done, we’ll consider the removal of the login itself and also enlist the actions that have to be done.

在以下各节中，我们将首先定义并构建一个测试用例。 我们的目标是删除SQL Server登录名[ApplicationSQLUser1]及其对应的数据库用户，都称为[UserA] 。 我们将首先开始尝试从[Db1]数据库中删除[UserA]数据库用户。 一旦我们成功执行了此任务，我们将回顾我们遵循的步骤并考虑其适当的顺序。 完成此操作后，我们将考虑删除登录名本身，并征集必须执行的操作。

测试用例情况 (Test case situation)

Explanation

说明

In this section, we will present the situation to which we will refer in this article where we want to drop a SQL Server login called [ApplicationSQLUser1], that is mapped to two SQL Server databases, [Db1] and [Db2]. This mapping is done between this login a database user called [UserA] in [Db1] and [dbo] in [Db2]. Actually, database [Db2] is owned by [ApplicationSQLUser1] login. There is also another login called [ApplicationSQLUser2] that is mapped to [UserB] in [Db1] database.

在本节中，我们将介绍在本文中要引用的情况，我们要删除一个名为[ApplicationSQLUser1]SQL Server登录名，该登录名已映射到两个SQL Server数据库[Db1]和[Db2] 。 此映射是在登录名[Db1]中的 [UserA ]和[Db2]中的 [dbo]的数据库用户之间完成的。 实际上，数据库[Db2]由[ApplicationSQLUser1]登录名拥有。 还有另一个名为[ApplicationSQLUser2]的登录名，该登录名已映射到[Db1]数据库中的[UserB] 。

There are some other important facts:

还有其他一些重要事实：

• • [ApplicationSQLUser1] login is the owner of [ApplicationSQLUser1]登录名是[Db3] database [Db3]数据库的所有者
  • [ApplicationSQLUser1] login is granted [ApplicationSQLUser1]登录名被授予CONNECT SQL permission and can grant it to any SQL Server Login. CONNECT SQL权限，并且可以将其授予任何SQL Server登录名。
  • [ApplicationSQLUser1] login granted [ApplicationSQLUser1]登录授予CONNECT SQL permission to CONNECT SQL权限[ApplicationSQLUser2] login [ApplicationSQLUser2]登录
  • [ApplicationSQLUser1] login is the owner of a Server Role called [ApplicationSQLUser1]登录名是名为[ServerRoleOwnedByUser1]. [ServerRoleOwnedByUser1]的服务器角色的所有者。

• [Db1] dat