SQL Server 2005 DROP LOGIN syntax error

Question

This snippet of code (it's just a repeater, not the actual code I'm going to run) throws a syntax error on the DROP LOGIN command:

declare @obso_user varchar(16)
set @obso_user = 'BEN_VA\20362781'
DROP LOGIN @obso_user

Does anyone know why?

Answer 1

You can't use a variable with DROP LOGIN. To do this you'll need to use dynamic SQL:

DECLARE @obso_user VARCHAR(16)
SET @obso_user = 'BEN_VA\20362781'

DECLARE @sql NVARCHAR(100)
SET @sql = N'DROP LOGIN [' + @obso_user + ']'
EXEC sp_executesql @sql

Put that in a CURSOR to do more than the single login:

DECLARE @obso_user VARCHAR(16)
DECLARE @sql NVARCHAR(100)

DECLARE USER_CUR CURSOR FOR
SELECT [Something] FROM User_Table

OPEN USER_CUR
FETCH NEXT FROM USER_CUR INTO @obso_user
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'DROP LOGIN [' + @obso_user + ']'
    EXEC sp_executesql @sql
    FETCH NEXT FROM USER_CUR INTO @obso_user
END

CLOSE USER_CUR
DEALLOCATE USER_CUR