November 4, 2009 at 12:37 pm
Is this possible?
Background:
High profile, high availability system, 2005 Enterprise Edition. The application uses SS authentication. Few individuals have NT access to the DB. Those limited individuals who have NT Authentication, have been given strict instructions to limit queries to off-peak hours, because certain queries have brought down the database during peak hours, causing the DBAs to be burned in effigy.
Is there a way to put a time constraint around SQL Server security, where we could allow the users read-only rights during the hours of 8pm - 4am? or similar?
November 4, 2009 at 2:19 pm
If you have SQL2005 SP2 or higher have a look at http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2009 at 7:00 am
Thanks, this is a great article! THANK YOU!
This particular instance is SP1, but the alternatives mentioned in the article would work... EXCEPT for one issue. The instance contains multiple databases, only one of which I want to restrict daytime access. Is there a way at the database level to limit access rather than at the logon level?
November 5, 2009 at 7:35 am
Currently SQL2005 is on SP3 for a while now ! You should evaluate it and upgrade if you can !
The logon trigger (available with sp2) is only validated a logon time !
If someone changes database after that "use TheOtherDb" that user will not be triggering a logon trigger again for that same connection session !
You could also create a db-group e.g. "uRole_workinghours", grant that group all needed auth on the db, add all needed accounts to that group (and off course revoke all auth they allready have).
Now schedule a job that denies reading to that group at a certain time.
-- deny read auth
EXEC sp_addrolemember N'db_denydatareader', N'uRole_workinghours'
-- remove deny (so re-enable normal auth)
EXEC sp_droprolemember N'db_denydatareader', N'uRole_workinghours'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply