Execute output from query

Question

I've got this query that builds my drop login statements. As part two I want to execute this output. Ideally I want to do this in a sql job with two steps (generate and execute). Can this be done?

SELECT 'DROP LOGIN [' + name + ']'
FROM [master].[dbo].[syslogins]
WHERE isntgroup = 0
  AND isntuser = 0
  AND sysadmin = 0
  AND name != 'sa'
  AND name NOT LIKE '##%'
ORDER BY name

Answer 1

What you can do is set the output of your query to concat a string variable. Something like the following:

declare @sql_command varchar(max);
set @sql_command = '';

SELECT @sql_command += 'DROP LOGIN [' + name + ']' + char(13)
FROM [master].[dbo].[syslogins]
WHERE isntgroup = 0
  AND isntuser = 0
  AND sysadmin = 0
  AND name != 'sa'
  AND name NOT LIKE '##%'
ORDER BY name

print @sql_command;
-- exec (@sql_command);

The PRINT will show you your command, and I've commented out the EXEC but that'll run the generated SQL. Note, you'll have to uncomment the EXEC for anything to actually happen (be wary beforehand, and ensure this is the behavior you're looking for).

As for the one step to generate, and the other to execute, I'm not quite sure what you'd gain by doing that. It doesn't sound like you're giving yourself time to "review" the drop statements prior to execution. But, either way, you can store the statements in a table in one step then execute the recently generated DROP LOGIN commands in a subsequent step if you so choose. That'd be one way to go about that requirement.

I realize you probably have a requirement for the above task, but please note that you are asking to create a SQL Server Agent job to do something not-so-routine such as dropping server principals. This may call for seeing why there is actually a requirement and working on the situation at the cause of needing to drop logins rapidly on a reoccurring basis. Again, just something to think about.