1

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?

1 Answer 1

0

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
3
  • Thanks. A co-worker showed me the EXECUTE() command, and I used that.
    – RonJohn
    Feb 14, 2014 at 21:51
  • Regarding cursors: that's exactly what I was doing. :) The snippet was just a simplified "repeater" to show the error.
    – RonJohn
    Feb 14, 2014 at 21:53
  • Cool. Regarding EXECUTE() vs. sp_executesql - know that sp_executesql allows for query plan reuse and also parameterized statements (which help to prevent SQL injection attacks).
    – squillman
    Feb 17, 2014 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.