即使在登录时也删除 SQL 登录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4965260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop SQL login even while logged in
提问by EMP
When dropping a SQL Server 2008 login as part of integration test execution I sometimes get the following error:
当作为集成测试执行的一部分删除 SQL Server 2008 登录时，我有时会收到以下错误：
System.Data.SqlClient.SqlException: Could not drop login 'SomeTestUser' as the user is currently logged in.
System.Data.SqlClient.SqlException：无法删除登录“SomeTestUser”，因为用户当前已登录。
I don't care if it's logged in - I still want to drop it. What's the easiest way to do this?
我不在乎它是否已登录 - 我仍然想放弃它。什么是最简单的方法来做到这一点？
回答by EMP
OK, here's the script I came up with, which worked for me. Note that you need to be a member of the processadminserver role to find and kill the connection and a member of securityadminto drop the login. (Of course, sysadmincan do anything.)
好的，这是我想出的脚本，它对我有用。请注意，您需要成为processadmin服务器角色的成员才能找到并终止连接，并且需要成为securityadmin的成员才能删除登录。（当然，系统管理员可以做任何事情。）
DECLARE @loginNameToDrop sysname
SET @loginNameToDrop = '<victim login ID>';
DECLARE sessionsToKill CURSOR FAST_FORWARD FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = @loginNameToDrop
OPEN sessionsToKill
DECLARE @sessionId INT
DECLARE @statement NVARCHAR(200)
FETCH NEXT FROM sessionsToKill INTO @sessionId
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Killing session ' + CAST(@sessionId AS NVARCHAR(20)) + ' for login ' + @loginNameToDrop
SET @statement = 'KILL ' + CAST(@sessionId AS NVARCHAR(20))
EXEC sp_executesql @statement
FETCH NEXT FROM sessionsToKill INTO @sessionId
END
CLOSE sessionsToKill
DEALLOCATE sessionsToKill
PRINT 'Dropping login ' + @loginNameToDrop
SET @statement = 'DROP LOGIN [' + @loginNameToDrop + ']'
EXEC sp_executesql @statement
回答by pasx
In SqlServer Studio on the Master DB.
在主数据库上的 SqlServer Studio 中。
Use the command sp_who2 to list the opened sessions.
使用命令 sp_who2 列出打开的会话。
In the list find the spid for your user - there may be more than one - e.g. 999
在列表中找到您的用户的 spid - 可能不止一个 - 例如 999
Use kill and the spid to close all the sessions e.g.: kill 999
使用 kill 和 spid 关闭所有会话，例如：kill 999
Then DROP LOGIN [theuser]
然后删除登录 [theuser]
回答by Darren G
User can be deleted after killing the session by identifying the session_idof the user.
通过识别session_id用户的身份，可以在杀死会话后删除用户。
SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test'
KILL 69 --69 is session_id here, you may get different id
Now you can delete the login simply by executing the below query (or) by using sql server management studio options.
现在，您只需使用 sql server management studio 选项执行以下查询（或）即可删除登录名。
DROP LOGIN 'test'
回答by Tiele Declercq
This may not always be applicable but I've just restarted the SQL server from services. That cleared up any open connections.
这可能并不总是适用，但我刚刚从服务中重新启动了 SQL 服务器。这清除了任何打开的连接。
回答by SqlACID
If you know what database they will be connected to, you could set the database to single user mode, with rollback immediate, that'll drop their sessions.
如果您知道他们将连接到哪个数据库，您可以将数据库设置为单用户模式，立即回滚，这将删除他们的会话。
回答by Kris Ivanov
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
as SqlACID said, or you could find all processes and kill one at time and then drop user
正如SqlACID所说，或者你可以找到所有进程并一次杀死一个然后删除用户
