How to control number of users in a database?

[kai]

Hi,
I use VB 2008 as front end, SQL Server 2005/2008 as back end, I want
control number of cucurrent users log on to the database, I am trying to use
a stored procedure, but it did not work. Is this possible? Please help.

Thanks

Kai

[Erland Sommarskog]

Re: How to control number of users in a database?

kai (kailiang@bells outh.net) writes:

I use VB 2008 as front end, SQL Server 2005/2008 as back end, I want
control number of cucurrent users log on to the database, I am trying to
use a stored procedure, but it did not work. Is this possible? Please
help.

If you actually mean *database* I don't think there is a foolproof
way to do this. You could set up a logon trigger that checks the current
database and compares this the number of connections active in the database,
and if the magic number is exceeded raises an error.

However, if the user would first connect to, say, the master database,
then issue a USE for the database in questions, in which case your trigger
wouldn't catch him.

On the other hand, if you wish to restrict the number of connections to
the entire *server*, this is very easy:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'user connections', 50
RECONFIGURE

I would advice you to be very careful with this. If your application
uses multiple connections, you may find that 50 connections scales down
to a maximum of 10 users.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Andy M]

Re: How to control number of users in a database?

On Oct 7, 8:06 am, "kai" <kaili...@bells outh.netwrote:

Hi,
 I use VB 2008 as front end, SQL Server 2005/2008 as back end, I want
control number of cucurrent users log on to the database, I am trying to use
a stored procedure, but it did not work. Is this possible? Please help.
>
Thanks
>
Kai

Hi Kai,

As part of your application's login, you might want to try querying
sys.sysprocesse s. In SQL 2005/2008, this is a system view that shows
information on all current connections to your SQL Server Instance.

http://msdn.microsoft.com/en-us/library/ms179881.aspx

When your VB app connects to the database, you are able to supply the
program_name as part of the connection string-- this application name
is available in sysprocesses. I presume that your interest in
counting connections is so that you can control licensing of your
application. You can use this basic query to count connections by
your application:

SELECT COUNT(*)
FROM master.dbo.sysp rocesses
WHERE program_name = 'MyApplication'

If your application creates multiple connections to the database, you
might end up counting that single user more than once. If that's the
case, you might need to try a variation utilizing other columns in
sysprocesses.

SELECT COUNT(DISTINCT hostname)
FROM master.dbo.sysp rocesses
WHERE program_name = 'MyApplication'

SELECT COUNT(DISTINCT nt_username)
FROM master.dbo.sysp rocesses
WHERE program_name = 'MyApplication'