Time Based Update Privilege Control - SQL2K EE

  • Hi All,

    I have a requirement where some users needed UPDATE privileges for a

    specific time during the night on couple of tables. Is this feature

    available in SQL Server 2000 EE? I don't want to give rights all day because

    these are production tables getting accessed by the system during the day

    time.

    To my understanding "no". But please let me know if there is any feature

    other than automating this script in a job for GRANT & REVOKE.

    Thanks & Regards,

    Murali Damera

    .

  • There's 3 options to acheive this:

    1. Only allow updates through a stored procedures that check the time of day before updating.

    2. Put triggers on the tables to check the time of day.

    3. If the updates are simple 1-table updates you may be able to restrict updating through time-based views. Eg.

     
    
    CREATE VIEW vwAuthors
    AS
    SELECT * FROM Authors
    WHERE DATEPART(Hour, GETDATE()) IN (20,21,22) -- for example
    WITH CHECK OPTION
    GO


    REVOKE ALL ON Authors FROM MyUsers
    GRANT SELECT ON Authors TO MyUsers
    GRANT ALL ON vwAuthors TO MyUsers

    Cheers,

    - Mark


    Cheers,
    - Mark

  • Another way might be to change their role membership via a job. At the start of the window add them to the role that allows update, at the end of the window remove them (and kill their connection).

    Could also give them a sql login/password. At the start of the window reset the password to the one you gave them, change it back to some other value at the end of the window.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • ...ok, there's 5 options to acheive this.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • I am sorry for the delay in replying... But I have implemented Andy's suggestion of having an additoinal role and adding to this role based on time thru job.

    Thanks Again to mccork & Andy for your suggestions.

    .

  • It looks that "sp_denylogin" and "sp_grantlogin" also are option.

  • No I cannot deny because this user needs READONLY access to the tables all the time.

    .

Viewing 7 posts - 1 through 6 (of 6 total)

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