Drop all logins where loginname like

Question

I'm looking to bulk remove users from a database with a script for every login with a name that matches a certain pattern. What is the best way to do this with TSQL?

DELETE syslogins WHERE.... does not work

Trying to delete straight from syslogins will return "Ad hoc updates to system catalogs are not allowed"

Solution 1

How about this query to create your TSQL statements? Then copy this SQL into a new query and exec.

select 'drop login [' + name + '];'
from sys.server_principals 
WHERE name like 'foo%'

This is basically the script that'll be created and run when you delete/drop a login from SSMS.

Solution 2

USE MyDatabase

DECLARE @LoginName sysname
DECLARE @SQL NVARCHAR(1000)

DECLARE DBLOGINS CURSOR FOR
    SELECT name FROM sys.database_principals
    WHERE name LIKE 'some_pattern%'

OPEN DBLOGINS

FETCH NEXT FROM DBLOGINS INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP LOGIN [' + @LoginName + ']'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM DBLOGINS INTO @LoginName
END

CLOSE DBLOGINS
DEALLOCATE DBLOGINS

Comments

[Russell Steen]

Thanks p. If nothing better comes along I'll do it this way. I was hoping to avoid the loop, but if it can only be done one at a time that's what I'll do.