Limiting number of database connections per user

Hello,
I would like to find out if it is possible in SQL Server 2000 to limit the
number of concurrent database connections per user. I am new to SQL Server,
and not sure if it is possible to limit it through some sort of parameter.
Any help would be greatly appreciated.
Thank you,
DmitriyNo way to limit the number of concurrent database connections PER USER.
Use the user connections option to specify the maximum number of
simultaneous user connections allowed on Microsoft? SQL Server
use SELECT @.@.MAX_CONNECTIONS to determine the maximum number of user
connections that your system allows
see "Setting Configuration Options" in BOL
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dmitriy" wrote:

> Hello,
> I would like to find out if it is possible in SQL Server 2000 to limit the
> number of concurrent database connections per user. I am new to SQL Server
,
> and not sure if it is possible to limit it through some sort of parameter.
> Any help would be greatly appreciated.
> Thank you,
> Dmitriy|||Thank you for your quick response.
Spasibo,
Dima
"Aleksandar Grbic" wrote:
> No way to limit the number of concurrent database connections PER USER.
> Use the user connections option to specify the maximum number of
> simultaneous user connections allowed on Microsoft? SQL Server
> use SELECT @.@.MAX_CONNECTIONS to determine the maximum number of user
> connections that your system allows
> see "Setting Configuration Options" in BOL
>
>
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Dmitriy" wrote:
>|||Hi
you can use the table "sysprocesses" to get the information abt the
connections to the database , and there by u can restrict each users
programatically
Renjith
"Dmitriy" wrote:

> Hello,
> I would like to find out if it is possible in SQL Server 2000 to limit the
> number of concurrent database connections per user. I am new to SQL Server
,
> and not sure if it is possible to limit it through some sort of parameter.
> Any help would be greatly appreciated.
> Thank you,
> Dmitriy

Limiting concurrent execution of a T-SQL stored procedure

Hello,

I have a quite large set of legacy stored procedures. The stored procedures are not reentrant, i.e. can not safely be executed in parallel by multiple threads. The procedures are not called multiple times simultaneously in the system, because of this restriction. There's however no explicit constraint to prevent multiple calls to these stored procedures and thus it's possible to e.g. accidentally call the procedures many times simultaneously.

My problem is, how to prevent multiple simultaneous executions of the stored procedures? Is there a native way to do this? In modern programming languages there are language constructs to prevent simultaneous access to code and variables (synchronized in Java, lock in C#). Is there something similar in T-SQL? What could be a workaround?

One options could be to use SQL Server jobs to prevent simultaneous accesss - only one instance of a job can be running at time. This is, however, problematic since if the job cannot be started because another instance is already running, the second instance will never be started. This is not what I won't.

JM

Refer to Books Online about the use of sp_getapplock and sp_releaseapplock.

Also, refer to this post. And this one.

|||

Thanks Arnie for your quick and good answer!

r,

JM

|||

Yes..

You can achive it using few tricks..using sp_getapplock.

But you need a wrapper storedproc or batch for this. Check the following example it will help you to desing your wrapper sp/batch statement.

Sample:

Code Snippet

Create table MyTable

(

RowId int identity(1,1),

HitStartedAt datetime,

HitTimestamp datetime,

UserName varchar(100)

)

Go

Create proc LegacyProc (@.user varchar(100), @.CalledTime datetime)

as

Begin

Insert Into MyTable

Values(@.CalledTime, getdate(), @.user);

--To wait for 10 sec : not required for your procedures, producing the latency to check the concurrent users action

WAITFOR DELAY '000:00:10'

End

Go

Create Proc MyProc

(

@.user varchar(100)

)

as

Begin

Declare @.PorcName as NVarchar(1000), @.CalledTime datetime

Begin Tran

--To get the Current SP Name, it should be unique for each SP / each batch

SET @.PorcName =object_name(@.@.ProcID)

SET @.CalledTime = Getdate()

--Lock the Current Proc

Exec sp_getapplock @.Resource = @.PorcName, @.LockMode = 'Exclusive'

--Execute Your Legacy Procedures

Exec LegacyProc @.user, @.CalledTime

--Release the lock

Exec sp_releaseapplock @.Resource = @.PorcName

Commit Tran

End

Limit remote connections

Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
Bing
Hello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan
|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan

Limit remote connections

Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I b
e
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan

Limit remote connections

Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan