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.