Agent Jobs

Take Database Offline And Disable All Associated Logins

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%'
image001

image001

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s