Block Remote Users

From time to time I am in a situation where I want to freeze all databases. To be more precise: I need to seriously lockdown all access.
For this I use a logon trigger. The logon trigger issues a Roll Back if the logon is being issued from a remote machine and the user is not in the exclude list. This gives me the flexibility to kick all services & users accessing the database, while keeping all my options open as I can still access the server and do anything with it. Below the Trigger I use. Please test on a demo machine first. Remember: you’ll still be able to logon to the server locally and remove the trigger. If nothing else works you can logon with DAC to remove the trigger.

USE MASTER
GO
–Create the trigger
CREATE TRIGGER BlockRemoteUsers ON ALL SERVER
WITH EXECUTE AS ‘sa’
FOR LOGON
AS
BEGIN
IF ‘<local machine>’ <> EVENTDATA().value(‘(/EVENT_INSTANCE/ClientHost)[1]’,
‘nvarchar(512)’)
AND EVENTDATA().value(‘(/EVENT_INSTANCE/LoginName)[1]’,
‘nvarchar(512)’) NOT IN
(‘mydomain\user1’, ‘mydomain\user2′,’mydomain\user2’)
BEGIN
ROLLBACK ;
END
END
GO

–Show the sessions that are still connected
SELECT  *
FROM    sys.dm_exec_sessions
WHERE   session_id > 50
AND session_id <> @@spid
AND host_name <> @@servername
AND login_name NOT IN (‘mydomain\user1’, ‘mydomain\user2′,’mydomain\user2’)

–Kill the sessions that are still connected
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = ”
SELECT  @cmd = @cmd + ‘KILL ‘ + CAST(session_id AS VARCHAR(50)) + CHAR(13)
+ CHAR(10)
FROM    sys.dm_exec_sessions
WHERE   session_id > 50
AND session_id <> @@spid
AND host_name <> @@servername
AND login_name NOT IN (‘mydomain\user1’, ‘mydomain\user2′,’mydomain\user2’)
EXEC ( @cmd
)
GO

–Drop the trigger
DROP TRIGGER BlockRemoteUsers ON ALL SERVER

Advertisements

About sqlsmurf
MS SQL Administrator

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

%d bloggers like this: