Revoking privileges from a user in SQL on specific days or times

24 Sep 2023

In some scenarios, it may be necessary to restrict a user’s access to certain database privileges during specific days or times. This can help ensure data integrity and security by limiting access when it is not required. In this blog post, we will explore how to revoke privileges from a user in SQL Server on specific days or times using a combination of SQL queries and scheduling mechanisms.

Step 1: Create a Time-based SQL Job

The first step is to create a time-based SQL job that will run at the specified time and day(s) to revoke the privileges from the user. SQL Server Agent can be used to create and manage jobs within SQL Server.

USE msdb;

-- Create a new SQL job
EXEC dbo.sp_add_job
    @job_name = 'Revoke Privileges Job',
    @description = 'Job to revoke privileges from a user at specific times';

-- Add a job step to revoke privileges
EXEC dbo.sp_add_jobstep
    @job_name = 'Revoke Privileges Job',
    @step_name = 'Revoke Privileges Step',
    @subsystem = 'TSQL',
    @command = '
        -- SQL query to revoke privileges from the user
        REVOKE ALL PRIVILEGES FROM <user_name>;
    ';

-- Schedule the job to run at specific days and times
EXEC dbo.sp_add_schedule
    @schedule_name = 'Daily Revoke Schedule',
    @freq_type = 4, -- daily schedule
    @freq_interval = 1, -- every 1 day(s)
    @active_start_time = 080000, -- start time: 08:00:00
    @active_end_time = 170000; -- end time: 17:00:00

-- Attach the schedule to the job
EXEC dbo.sp_attach_schedule
    @job_name = 'Revoke Privileges Job',
    @schedule_name = 'Daily Revoke Schedule';

-- Enable the job
EXEC dbo.sp_update_job
    @job_name = 'Revoke Privileges Job',
    @enabled = 1;

Make sure to replace <user_name> with the actual username for whom you want to revoke privileges.

Step 2: Monitor the Job Execution

Once the job is created and enabled, it will automatically run at the specified time and day(s), revoking the privileges from the user. You can monitor the job execution and view the job history by querying the sysjobhistory table in the msdb database.

USE msdb;

-- Query job history for the revoke privileges job
SELECT 
    j.name AS [Job Name],
    js.step_name AS [Step Name],
    jh.run_date AS [Run Date],
    jh.run_time AS [Run Time],
    jh.run_status AS [Run Status]
FROM 
    dbo.sysjobhistory jh
    INNER JOIN dbo.sysjobs j ON (jh.job_id = j.job_id)
    INNER JOIN dbo.sysjobsteps js ON (jh.job_id = js.job_id AND jh.step_id = js.step_id)
WHERE 
    j.name = 'Revoke Privileges Job'
ORDER BY
    jh.run_date DESC,
    jh.run_time DESC;

Conclusion

By following the steps outlined in this blog post, you can easily schedule a job to revoke privileges from a user in SQL Server at specific times or days. This adds an extra layer of security and control over user access to the database. Remember to adjust the schedule and query to fit your specific requirements.

#SQL #Privileges #UserAccess