SQL Issues and Queries

SQL Issues and Queries

USE [master]
GO
/****** Object: Login [NT SERVICE\SQLTELEMETRY] Script Date: 6/26/2017 12:52:06 PM ******/
DROP LOGIN [NT SERVICE\SQLTELEMETRY]
GO

-- Msg 15434, Level 16, State 1, Line 5
-- Could not drop login 'NT SERVICE\SQLTELEMETRY' as the user is currently logged in.

DECLARE @loginNameToDrop sysname
SET @loginNameToDrop = 'NT SERVICE\SQLTELEMETRY';

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 + ']'
SELECT @statement
EXEC sp_executesql @statement