The following SQL logic takes a database offline, and then disables all subsequent logins of the database that have a name that is associated with the database name. In this case we are using a wild card. In this example we are using an old TRACKIT database. This logic is useful in cases where you are decommissioning old database environments, but don’t necessarily want to drop any Databases or their users. A traditional approach is to simply take the database offline, and disable it’s users. Then after a period of 30 days you can decommission the database by removing it and the users. I’ve included a couple quick queries to confirm the database has been taken offline, and the logins have been disabled including a timestamp when the logins were modified.
-- take the database offline alter database [TRACKIT8] set offline with rollback immediate -- disable all logins associated with same database name using wildcard declare @disable_trackit_logins varchar(max) set @disable_trackit_logins = '' select @disable_trackit_logins = @disable_trackit_logins + 'alter login [' + name + '] disable;' + char(10) from syslogins where name like 'track%' order by name asc exec (@disable_trackit_logins) --for xml path(''), type -- confirm database is offline select 'database' = upper(name) , 'is_offline' = state_desc from sys.databases where name like 'trackit%' -- confirm all logins have been disabled select 'login' = upper(name) , 'is_disabled' = case is_disabled when 0 then 'Enabled' when 1 then 'Disabled' end , 'modified_on' = left(modify_date, 19) from sys.sql_logins where name like 'Trackit%'
Categories: Agent Jobs, Alerts, Automations, Backup & Restore, Command Prompt, Database Mail, Database Mirroring, Featured, Forensics & Auditing, High Availability, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds