| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2002-05-07 : 04:22:45
|
| hi i have a vb app.. i want only a fixed number of user can access the database....if the number exceed it should not login.i.eSELECT status ,hostname ,program_name ,physical_io ,db_name(dbid) , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char' from master.dbo.sysprocessesusing this query i can get the count of users connect.can we stop the sql login if it exceed the countthanks ======================================Ask to your self before u ask someone |
|
|
mohamedyousuff@yahoo.com
Starting Member
24 Posts |
Posted - 2002-05-07 : 05:03:34
|
It would be better if it is handled automatically by SQLServer. In Enterprise Manager, goto the server properties, Connections tab. You will find an option 'Maximum concurrent user connections'. Set it to the preferred value. You can also do this by sp_configure stored procedure. Execute sp_configure 'user connections'and find the current value.Hope that may be of help for your problem.S.Mohamed Yousuffquote:
hi i have a vb app.. i want only a fixed number of user can access the database....if the number exceed it should not login.i.eSELECT status ,hostname ,program_name ,physical_io ,db_name(dbid) , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char' from master.dbo.sysprocessesusing this query i can get the count of users connect.can we stop the sql login if it exceed the countthanks ======================================Ask to your self before u ask someone
|
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-08 : 01:39:45
|
| thanks mohamedyousuff@yahoo.com thanks for info...but that can be altered by the users (the client of my software)latteri need to have a procedure which stop from several login....other than sp_configure 'user connections' which can be alter any time i need to have something which can be change by me alone...======================================Ask to your self before u ask someone |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-08 : 08:42:11
|
| The only way I can think of is to disallow the clients from having sysadmin and serveradmin priviliges. That would prevent them from running sp_configure/RECONFIGURE. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-09 : 02:32:10
|
| thanks a lot for info...my application is a product which will have a limited users and will be installed at client side (customer ) and the database will be maintain by the client.. so the dba at client side can priviliges and increace the number... any database/ server setting can be change byt he dba. so i am looking for a method or process throught i can deny acess..if i write in vb for exampleexecute sp_who and count the no of user currently using my database and if the number less than allow login or else exit... can this process be done at server side... so that no other app can access my database...thanks======================================Ask to your self before u ask someone |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-09 : 08:13:43
|
The problem is if the client has sysadmin rights, they can simply disable anything you put in place to limit the user connections. Not to mention that it's unsatisfactory for someone to connect to the server and then get disconnected 10, 15, 30 seconds later. If I was an end user, that would infuriate me. You can bet the client's sysadmin will tolerate exactly 2 complaints about that before he or she goes off to remove that feature! If you can't prevent them from connecting in the first place, you'll only make the situation worse. Annnnnnd....quote:
so the dba at client side can priviliges and increace the number...
If your application gives them the ability to increase the number, then what's the point of limiting the connections anyway? |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-10 : 06:18:38
|
| thanks a lot guys... gr8we have a application which is sold on certain contract bases..(at any given time only N no of user can access App). nothing to do with the App or database... so while installing the app whe no of seats are fixed and cannot be changed if so every the client feel like increasing it he should contact us... so it a know thing so nothing to unsatisfactory for someone ... we have diffrent version like.. ex10 PC only5 user online only and no limitso second one i have to implement.thanks======================================Ask to your self before u ask someone |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-05-10 : 14:34:35
|
| Khalik,One problem with relying on sp_who is how do you define a user? I can be one user, but have multiple open connections and therefore multiple rows in sp_who.I would suggest you create a login procedure. Then make your own table to track who is logged in. Then, don't allow another login when the number of people logged in reaches the max. Have a logout step, or execute your logout logic when they close the VB app, to remove them from the table. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-13 : 03:56:35
|
| thanks AjarnMark... i do have a table which keep track of the current users... but if the client side have programmers who can access databse and generate some reports... then my app cannot stop them...i want sql to stop them from doing that.....from my app one user only one connection. (100%)suggestion tips tricks anything welcomethanks======================================Ask to your self before u ask someone |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-13 : 07:42:47
|
quote:
but if the client side have programmers who can access databse and generate some reports... then my app cannot stop them...
I was sort of suggesting that earlier. The ONLY way you can enforce this user limitation is to NOT ALLOW the client sysadmins the ability to change these settings. You can't give them the keys to the kingdom and expect them to leave everything alone.Here's a question for you: do they NEED, and I mean NEED, sysadmin or serveradmin permissions? What do they need to do that REQUIRES, and I mean REQUIRES, that level of access? Backups can be done through fixed database roles, jobs can be managed using the processadmin role, etc. If you can't cut them out of sysadmin or serveradmin, then forget the whole thing. The only other thing I can think of is to make it a part of your service contract with them; you know how many licenses they have, if they increase the number, that voids their contract with you. This is not really enforceable, but it's a good scare tactic. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-13 : 23:24:32
|
quote:
Here's a question for you: do they NEED, and I mean NEED, sysadmin or serveradmin permissions? What do they need to do that REQUIRES, and I mean REQUIRES, that level of access? Backups can be done through fixed database roles, jobs can be managed using the processadmin role, etc. If you can't cut them out of sysadmin or serveradmin, then forget the whole thing. The only other thing I can think of is to make it a part of your service contract with them; you know how many licenses they have, if they increase the number, that voids their contract with you. This is not really enforceable, but it's a good scare tactic.
thanks a lot robvolk . to be frank i dont know about sysadmin... serveradmin... and database roles. not much in DBA .... still a programmer . so from a programmer point of view is there a way some function SP or some event by which we can do with out any DBA stuff to be simple and straight... .. quote:
You can't give them the keys to the kingdom and expect them to leave everything alone
what my boss say is it should look like they have the key to the kindom and they should not be able to do thing againt our wish.. he hee so we have to implement it by code so that no one can change...======================================Ask to your self before u ask someone |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-13 : 23:33:06
|
| Read up on roles, both database and server roles, permissions, etc. to see what each kind does. If you do not want them to modify anything, you can specifically revoke CREATE TABLE/PROC/VIEW permissions, etc. It will help to make a list of what they NEED to do. They will probably need SELECT permissions, UPDATE/DELETE/INSERT also. Any server maintenance like backups, replication, job creation or scheduling, should be considered off-limits. I can't suggest anything else; you're the one designing the application, you have to determine what functions they need to do. If anything actually REQUIRES sysadmin or serveradmin level, then you're SOL. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-14 : 04:27:31
|
| thanks a lot robvolk.. that what in details gr8 one more small thing just a small one.. can we stop them from creating users... by creating few roles for backup..restore and application requiessome thing like we will be the super user.. the local DBA will have only those rights which we have assign... then might me we can restrict a bit....thanks for all u help======================================Ask to your self before u ask someone |
|
|
|
|
Login ........