| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-10-04 : 12:43:31
|
| How can I limit a SQL Server account in order to login in a specific time period? I have some accounts for reports. They are ReadOnly accounts and just provide data for reports. I want to prevent them by limiting their access time during the business day. Also, I like to limit them by number of rows or CPU cycle that their reports produce/need. For example, if they are requesting a heavy report that needs ... of CPU cycles or huge number of rows, then the request fails or at least in a specific time of the day it fails.Is there a way to implement this?Canada DBA |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-04 : 12:53:12
|
| I'm not aware of any easy solution to this problem. Since SQL Server doesn't have a built in method for handling the type of feature you are looking for, you are going to have to craete it yourself. That is going to be a lot of work. Are you end users reporting from your production OLTP environment? If so, it would be advisable to simply create a reporting database that they can go crazy in. -ec |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2005-10-04 : 13:03:57
|
| How about if I want to limit their access time? Any way to implement it? I know we have this capability in Network environment. I am looking for something similar. Are you end users reporting from your production OLTP environment? If so, it would be advisable to simply create a reporting database that they can go crazy in. Yes, we do get on-line reports. I have created a seperate database for reporting and everynight an automated process copies all the tables from the original DB into the Reporting DB. But both DBs are located on same server. Don't you think a query that returns huge number of rows or needs much more CPU cycles may impact the production environment?Canada DBA |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-04 : 14:47:00
|
| yes, typically a reporting database is located on another physical server. However, even having a reporting database on the same box will be of benefit. You can alleviate lock contention and also control the memory allocation (if you use a separate instance). that way the reporting environment won't overwhelm your OLTP environment.-ec |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-04 : 17:35:46
|
| In SQL 2005, there are more security features that are "inherited" from the OS, such as password expiration and rules. You might want to research it to see if login times are also in there.---------------------------EmeraldCityDomains.com |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-04 : 22:34:55
|
| some options:1. you can schedule revoking rights in a job2. you can monitor sysprocesses for those logins at specified time and kill the spids corresponding to themHTH--------------------keeping it simple... |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-10-05 : 12:57:11
|
I like the 1st option. It's a very smart idea!quote:
Originally posted by jen
some options:1. you can schedule revoking rights in a job2. you can monitor sysprocesses for those logins at specified time and kill the spids corresponding to themHTH--------------------keeping it simple...
Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-10-05 : 13:00:36
|
| You can alleviate lock contention and also control the memory allocation (if you use a separate instance).Do you think if I create another instance in production and put all the reporting DBs in it, it will help my production? Is it better than just having a reporting DB in same default instance?Thanks, p.s. Is it advisable to have two or more instances in production?Canada DBA |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-05 : 13:13:31
|
quote:
Originally posted by farhadr
You can alleviate lock contention and also control the memory allocation (if you use a separate instance).Do you think if I create another instance in production and put all the reporting DBs in it, it will help my production? Is it better than just having a reporting DB in same default instance?
having a separate instance allows you to control the memory utilization. If you put a reporting database in the same instance as your OLTP environment, the reporting database can monopolize the buffer cache.-ec |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-05 : 22:24:34
|
if you're considering separating productions from reports,IMHO, this will be your best option, this way, users can really berestricted with read only and you can control the environmentwith less consideration to its impact to productions, you will also be able to track down users easily  --------------------keeping it simple... |
|
|
|
|
Limit a SQL Server account