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
-
...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