How do I limit user access to certain time of day

  • Craig A. Silvis

    Ten Centuries

    Points: 1038

    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.

  • jburkman

    Right there with Babe

    Points: 768

    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

    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

    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

    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

    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

    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

    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

    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

    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: 9370

    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

    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

    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

    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!

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

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