Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Yes it's called Dynamic SQL and RDBMS.Execute(string) can execute dynamic SQL; but caution is needed as this allows SQL injection –  xQbert May 30 '13 at 2:12

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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