Disable multiple logins in one step

Is there a way to disable multiple sql logins based on a set of users in a table? For example:

ALTER LOGIN (Select userid from tbl_user) DISABLE;

No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL:

DECLARE @sql nvarchar(max) = N'',
        @cmd nvarchar(550) = N'ALTER LOGIN $$ DISABLE;' 
                           + CHAR(13) + CHAR(10);

SELECT @sql += REPLACE(@cmd, N'$

When you're happy with the output, uncomment the EXEC.

Note that if you have a lot of users in that table, you won't be able to see the whole command using PRINT, since there are limitations in output length. See this tip for workarounds.

Also, there is no error handling here, so you should probably actually either add TRY/CATCH or join to sys.server_principals to make sure you are disabling logins that actually exist:

SELECT @sql += REPLACE(@cmd, N'$, QUOTENAME(userid))
  FROM dbo.tbl_user;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


When you're happy with the output, uncomment the EXEC.

Note that if you have a lot of users in that table, you won't be able to see the whole command using PRINT, since there are limitations in output length. See this tip for workarounds.

Also, there is no error handling here, so you should probably actually either add TRY/CATCH or join to sys.server_principals to make sure you are disabling logins that actually exist:

%%CODEBLOCK_1%%, QUOTENAME(p.name))
  FROM dbo.tbl_user AS u
  INNER JOIN master.sys.server_principals AS p
  ON p.name = u.userid;

, QUOTENAME(userid)) FROM dbo.tbl_user; PRINT @sql; -- EXEC sys.sp_executesql @sql;

When you're happy with the output, uncomment the EXEC.

Note that if you have a lot of users in that table, you won't be able to see the whole command using PRINT, since there are limitations in output length. See this tip for workarounds.

Also, there is no error handling here, so you should probably actually either add TRY/CATCH or join to sys.server_principals to make sure you are disabling logins that actually exist:

%%CODEBLOCK_1%%

StackExchange Database Administrators Q#196388, answer score: 8