Menu
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"
232
When you right click on a database and click Tasks and then click Detach Database , it brings up a dialog with the active connections. By clicking on the hyperlink under "Messages" you can kill the active connections. You can then kill those connections without detaching the database.
Once you have identified orphan users it is extremely simple to remove them. You remove them by using the sp_revokeuser SP. Here is an example that removes the database users 'USERX', from the current database in use.
If you are using SQL Server Management Studio you can browse to the user and right-click selecting delete.
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.
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
