Revoke Guest Access in SQL Server

On a Larger Scale of Security and Secured Environment, There is a need to revoke Guest Access to All users Databases on periodic basis to thighten the security.

Below is the script which will help you deal with it. All you need to do is run this script for once and on Weekly Basis, it will revoke Guest Access to All User Databases.

/*
REVOKE GUEST ACCESS FROM ALL USER DATABASES ON WEEKLY BASIS
This script will do the following
1. Create A Job
2. Add A step to Job with Revoke Guest access to all user databases
3. Add A weekly schedule to Job, so that Revoke guest Access Runs every week
*/
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
--ADD A CATEGORY TO HOLD OUR JOB
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Security' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Security'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO RollBackAndQuit
END

--ADD JOB TO RUN OUR REVOKE GUEST ACCESS
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'RevokeGuestAccess',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Job To Revoke Guest Access From All User Databases on Weekly Basis',
        @category_name=N'Security',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'YourDefinedOperatorName', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO RollBackAndQuit
--ADD JOBSTEP WITH ACTUAL SCRIPT TO RUN OUR REVOKE GUEST ACCESS
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RevokeGuestAccess_Access',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'DECLARE @RevokeSQL NVARCHAR(MAX)
SET @RevokeSQL = ''EXEC dbo.sp_MSForEachDB ''''USE [?]; IF db_id() NOT IN (1,2,3,4) REVOKE CONNECT FROM GUEST''''''
EXEC (@RevokeSQL)',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO RollBackAndQuit
--SET THE START UP STEP
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO RollBackAndQuit
--ADD SCHEDULE TO OUR JOB TO RUN ON WEEKLY BASIS
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RevokeGuestAccess_Weekly_Schedule',
        @enabled=1,
        @freq_type=8,
        @freq_interval=64,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20000101,
        @active_end_date=99991231,
        @active_start_time=20000,
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO RollBackAndQuit
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO RollBackAndQuit
COMMIT TRANSACTION
GOTO SUCCESSFUL
--ROLLBACK AND QUIT IF ANY OCCURS
RollBackAndQuit:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
SUCCESSFUL: