September 3, 2002 at 4:45 am
Hi i building a program and i need to only allow a certian number of people into a Database from my program.
The problem is i don't know if MS SQL Server 2000 can do this.
Is there a way i can check how many people are in one specific Database?
Thank you in Advanced for any help.
Please respond to this message or email me directly at
September 3, 2002 at 5:00 am
You can query master..sysprocesses where dbid=x. Or where db_name(dbid)='yourdb'. Then based on the count decided whether or not to allow the connection. Im curious why you're enforcing this restriction?
Andy
September 3, 2002 at 7:19 am
I'm trying to make a program that the price of it is based on the number of users allowed into the Database. I want it so they can install the software on as many machines as they like but only so many people are allowed in at one time.
Is this possible?
So i query the stored procedure sysprocesses with the database ID or Name? And that tells me how many people are in that Database?
Thank you for you help.
I shall try this ASAP.
September 3, 2002 at 7:43 am
There are two things that could cause you an issue with this approach. The first is the security model on your SQL Server - if that is set to SQL then you won't get unique user id's. The second issue is connection sharing. I assume your users are connecting over a network? If they are connecting via a webserver or Intranet then you're not going to get accurate user counts. It would be far easier to actually enforce a log-in procedure in your application than rely on the infra-structure to be consistent.
Regards
Simon
September 3, 2002 at 4:32 pm
As it stands everyone has to log in to be able to use it.
I want add something that once they have logged in, if the maximum number of users are in the database then Bring up a message and exit.
September 3, 2002 at 6:27 pm
I think if you just checked for the number of connections to the db along with the app name (to rule out maint jobs, etc) you'd be ok, assuming you maintain a constant connection to the db. Not recommended if you want to really scale high, but perfectly workable for small apps. Also assuming you only open one connection per user. If you use more, then just allow a multiple per user - 3 connections per user x 5 authorized users = 15 max connections.
Thought about how you will adjust the number of authorized users? This will probably be the attack point for someone trying to break it.
Andy
September 4, 2002 at 5:19 pm
Thank you everyone for your help and i've got it working well. I use the program_name and rule out any incorrect ones.
as the uppermost limit would probably be around 10 i don't really think its going to be a huge problem. I've tested it in the envoriment it will be used in and we've come up with no problems. (As of yet.)
I have come across another problem.
SQL can call to an exec. But i want to call to an exec that will then update stuff on an SQL Database. The only problem is what username and pasword will it use to login. I was wondering if SQL can pass that to it?
If not i'll just write the code in SQL but its just that i already have the code in EXECs.
Thank you Everyone espically you Andy for your help.
September 4, 2002 at 5:51 pm
Update in a different db from the first update? Same server?
Andy
September 5, 2002 at 6:12 am
Sorry i wansn't making myself very clear.
Basically using SQL Agent i run an overnight job. This job does certain things to one database. I want to call out to an exec which will run updates on this same Database. But to be able to update a database from an exec it needs to log into this database. As this is called from an overnight job what username and password would it use? I'm a bit stuck on this.
Thank you Andy
September 5, 2002 at 6:30 am
Options are to embed a sql password/login, or have it run under the agent account (NT) and make sure it has the appropriate permissions. I normally go with sql login for maint type apps.
Andy
September 5, 2002 at 4:27 pm
Hey cheers Any I'll think i'll implement that today. Cheers.
Sorry to keep asking you loads of Questions in one thread but do you know of any problems with MSDE's Server Agent as we can't get it to auto run when Os runs. The check box is greyed out. Its find on the SQL Server 2K and SQL Server but only on MSDE. is it an anoying bug they put in so people have to upgrade to SQL Server?
Cheers for all your help Andy.
September 5, 2002 at 7:39 pm
Not aware of any issues. Recommend you start a new topic, we'll see if anyone has ideas.
Andy
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply