Home > Database Administration, SQL Server > SQL Server DBA Quiz 2011 – How will you restrict users at certain period?

SQL Server DBA Quiz 2011 – How will you restrict users at certain period?

SQL Server DBA Quiz 2011 has started at www.beyondrelational.com. I think that  you can learn a lot from such quiz and at the same time you can test and verify your SQL Server administration skills.
One of the SQL Server DBA Quiz 2011 questions is: How will you restrict users at certain period?

Here is a detailed explanation of the question:
Assume that in your database there is a table named test and a user named A. During 10 AM to 1 PM each day, this particular user should be restricted to make any data changes(insert/update/delete) in the table test. You, as a DBA, are asked to implement this restriction. How will you implement this restriction?

I think that question is very interesting so I will give my solution here also.

My solution to the question:

Create trigger on table Test to prevent INSERT, UPDATE and DELETE statements for specified login and then disable it. Code example:

-- Create trigger to prevent user A to execute insert/update/delete on table test.
CREATE TRIGGER dbo.TriggerForUserA ON dbo.Test
FOR INSERT, UPDATE,DELETE
AS
-- Run this trigger if login is A.
IF SYSTEM_USER = 'A'
BEGIN
RAISERROR ('User A can''t insert/update/delete into table Test during 10 AM to 1 PM each day', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
GO
-- Disable Trigger
DISABLE TRIGGER dbo.TriggerForUserA ON dbo.Test;
GO

Trigger is created but disabled. Now, you need to create SQL server job to runs every day at 10 AM and at 1 PM. Job needs to enable trigger dbo.TriggerForUserA at 10 AM and disable it at 1PM.

Here is sample code for SQL Job step:

IF convert(varchar(5),GETDATE(),114) = '10:00'
BEGIN
ENABLE TRIGGER dbo.TriggerForUserA ON dbo.Test;
END
IF convert(varchar(5),GETDATE(),114) = '13:00'
BEGIN
DISABLE TRIGGER dbo.TriggerForUserA ON dbo.Test;
END

That is my solution. If you know some other way to resolve the problem you can post it in the comment.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: