How do I limit user access to certain time of day

Question

How do I limit user access to certain time of day

Craig A. Silvis

Ten Centuries

Points: 1038
More actions
July 24, 2008 at 9:38 am

#194335

We have a group of queries that we want to limit to run off peak hours to stop from bogging down production.
These queries pull data from our prod server and store the results on a reporting server.
We don't want the developers to run the queries during prime time
Our test server is an extremely small subset of the production data so to get a real idea of how long a query will run it needs to be run against prod - but off hours.
Is there any way to limit user access to the server to only certain times of day?
I was thinking that one way would be to limit their access to views that had code in it to check the time of day and return an error if during prime time, but that would have to be added to every view.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

jburkman Right there with Babe Points: 768 More actions · Answer 1

I'm no network guy, but if you are running NT authentication for db access can't the users (user group) at the NT level be granted access to a particular server on a time basis?

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 2

I'm not a network guy either so I was hoping someone else would know if it could be done and if so how to do it.

I know net nanny can do it! 😀

jburkman Right there with Babe Points: 768 More actions · Answer 3

So if you use Net Nanny, and have a server job that adds pr0n at 8 am, then removes it at 5 pm .....

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 4

How about this...

put the queries you want executed in the after house window inside a procedure or procdures. Create a user with explicit permissions to execute them. Deny execute to your DBA/users. Then schedule the procs in a job.

DAB

Herve Roggero Ten Centuries Points: 1230 More actions · Answer 5

Depending on the version of the SQL Server you are using you could use a DDL Trigger for Logon access. You can have full control on who/what/when/where. You will need SQL Server 2005 (SP2 I believe). Here is a good link about this technique:

http://blogs.technet.com/vipulshah/archive/2007/12/04/ddl-triggers-and-logon-triggers.aspx

Thanks

Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 6

I am running SQL 2005 so the LOGON Triggers, DDL Triggers looks like just the ticket.

I'll have to experiment on a test server to see how they operate but from what I read on that link it looks like exactly what I wanted.

Thanks!

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 7

I set up a user id called 'batchid' on a test server and created a logon trigger like this:

CREATE TRIGGER usrLoginCheck_LogonTrigger

ON ALL SERVER WITH EXECUTE AS 'batchid'

FOR LOGON

AS

BEGIN

declare @EarlyTime datetime,

@LateTime datetime,

@todays_date varchar(25)

set @todays_date = CONVERT(varCHAR(25),GETDATE(),110)

set @EarlyTime = Convert(datetime, @todays_date + ' 07:00:00.000')

set @LateTime = Convert(datetime, @todays_date + ' 23:00:00.000')

if ORIGINAL_LOGIN()= 'batchid'

and getdate() between @EarlyTime and @LateTime

ROLLBACK;

END

I tried to run an osql session to run a query from my desktop and it errored like I expected:

Logon failed for login 'batchid' due to trigger execution.

then I altered the trigger so the early time was after the current time (i.e. I was in the batch window and not during prime time) and the trigger allowed me to run the query.

steveb. SSC-Forever Points: 46733 More actions · Answer 8

I havent tried this before,

but have you thought about creating a job that runs each night and changes the permissions for those users to DENY, and then run another job that gives them permission back in the morning.

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 9

No I hadn't and for a sql 2000 instance that just may do the trick!

That's what I love about these forums - there are so many good ideas out there.

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 10

In a full Active Directory environment, you can define the "Logon Hours" for a particular user (found under the [Account] tab in ADUC).

With local logon (no AD) I don't think you can restrict a user to specific times (unless you use logon scripts, which would get messy).

I do like the ability to use a Logon Trigger in 2005 though, in this context it seems a smarter solution.

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 11

You can simply use SET QUERY_GOVERNOR_COST_LIMIT in the login trigger. You can check the currenttime of the server in the trigger and if it falls in your peak hours, set the option.

This option is used to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

See the following link For details...

http://msdn.microsoft.com/en-us/library/ms190419.aspx">

http://msdn.microsoft.com/en-us/library/ms190419.aspx

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 12

With SSRS 2005 you can create reports that load from cached data, and create scheduled subscriptions to create the cached data. Knowledgable users can get around this, but it is the method that we are currently using to limit report request processing time.

vbzarsuelo Grasshopper Points: 15 More actions · Answer 13

I know this thread has been inactive for 2 years but someone may need a solution and find his/her way here and I just found something that might be of help:

http://sqlsalt.blogspot.sg/2012/03/limit-instance-access-by-time-and-day.html

HTH!