SQL Server Performance Forum – Threads Archive

Limit a SQL Server account

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? CanadaDBA p.s.
I have created a seperate database for reporting and every night 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?

I think this can be done with Active Directory but I am interested to know how without using AD. With regards to your other concern, Reporting DB, is trans. replication not an option for you? or have a separate reporting server? Jon M

Login hours are limited only by windows accounts – in SQL 2000 you cannot impose login hours. I.e. you would have to issue users with a seperate windows account with limited login hours and access to the databases in question. I think you’re asking too much of SQL Server. Move your reporting database to another server.

Hi, if you are using Crystal Report for reporting you can use some tweaky option – its crual but you can force user to Export Report in excel etc. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

With regards to your other concern, Reporting DB, is trans. replication not an option for you? or have a separate reporting server? Trans Replication is one thing that we are thinking about. How about having a "Report instance" in the production? I mean the original DBs are located on default instance and the Report DBs are on "Report" instance. All are on same server and the "Report Instance" is updated every night. CanadaDBA

Getting another server may take long time for higher level managmenet but I am working on that, too.
What’s your idea about another instance on the same server? The "Report instance" that I have mentioned in my previous post. Does it help?

quote:Originally posted by mulhall I think you’re asking too much of SQL Server. Move your reporting database to another server.

CanadaDBA

I don’t think creating another instance will be much of a help since they will reside on the same server with the same HW config. If the report users will be reading some tables only, have them read directly to the production tables. But if your report users require huge amount of rows to be retrieved, another option might be to create another DB on the same production instance. Have a separate disk array for MDF and LDF. In other words, do the necessary optimization and fine tuning until you have a separate reporting server. Jon M

Multiple instances might help a little – on a multiprocessor server you could limit each instance to one or more CPUs, and divvy up the memory to prevent workloads from interfering with each other. If I understand the main issue correctly to be ensuring the reporting doesn’t knock out the main app.

Hi, here is a vb code test it in your environment , add lable (named lable1 to it),timer (named it timer1) <pre id="code"> Private Sub Form_Load() Label1.Caption = 0 Timer1.Interval = 1000 ‘ Set Timer interval. End Sub Private Sub Timer1_Timer() Label1.Caption = Val(Label1.Caption) + Timer1.Interval ‘ Update time display. If Val(Label1.Caption) > 10000 Then End End Sub </pre id="code"> <blockquote id="quote">quote:<hr height="1" noshade id="quote">it will kickoff User after 10 seconds , i have tested it in vb+crystal report<hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Why not just setup a job to lock/unlock sql users? It’s kind of round-about way to do things, but it should work. Live to Throw
Throw to Live

Because it’s not addressing the primary issue, it addresses the symptoms.

Ok. I read ‘limiting their access’ and thought ‘lock them out’. I reread the post and he wants to just ‘handicap’ the users account. Gotcha. Live to Throw
Throw to Live

Well without meaning to labour the point, the key issue is that running reports takes up too much resource. I hate it when people decide on a solution first and ask how to implement it, rather than analysing the core problem for the best solution. IMHO, the original post should state the whole issue and be asking for a solution to address this, rather than posting a solution. Happens all the time here.

I didn’t see anywhere in this post that he has spent a lot of time trying to optimize the reports. Has he exhausted all possibilities on making the reports more efficient? That would have been my first step. Other things to try: Defragement all drives(with SQL server shut down) – we like Diskeeper
update network drivers
find bottlenecks in network
add more memory
make sure SQL server sees extra memory
upgrade to faster drives
put log and data file on seperate devices Just some things off the top of my head
Live to Throw
Throw to Live

]]>