db in Single User mode yet can be accessed via odbc

Hi ,
How does the SINGLE USER actually works ,
i have used the follwing commands :
sp_dboption 'acb' , 'single user' 'true' and refresh
which it shows the the db : abc is now single user.
However , i can still access that db via odbc.
Is this how a single user shld works ? Not as wat in
the Books Online :
SINGLE_USER allows one user at a time to connect to the
database. All other user connections are broken. The
timeframe for breaking the connection is controlled by the
termination clause of the ALTER DATABASE statement. New
connection attempts are refused. The database remains in
SINGLE_USER mode even if the user who set the option logs
off. At that point, a different user (but only one) can
connect to the database
thkzSingle user means one user. That one user can connect through any interface,
including an odbc connection.
Are you possible confusing single user mode of a database with single user
mode of the whole SQL Server. This option you've described refers to a
single database. Maybe other connections are to other databases.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"maxz" <anonymous@.discussions.microsoft.com> wrote in message
news:272501c47061$cad93e30$a601280a@.phx.gbl...
> Hi ,
> How does the SINGLE USER actually works ,
> i have used the follwing commands :
> sp_dboption 'acb' , 'single user' 'true' and refresh
> which it shows the the db : abc is now single user.
> However , i can still access that db via odbc.
> Is this how a single user shld works ? Not as wat in
> the Books Online :
> SINGLE_USER allows one user at a time to connect to the
> database. All other user connections are broken. The
> timeframe for breaking the connection is controlled by the
> termination clause of the ALTER DATABASE statement. New
> connection attempts are refused. The database remains in
> SINGLE_USER mode even if the user who set the option logs
> off. At that point, a different user (but only one) can
> connect to the database
>
> thkz
>|||Hi,
You cannot set the database to single user mode usng the below comamnd if
any user is connected to that partcular database.
sp_dboption 'northwind','single user',true
There are 2 options to make the database single user:-
1. Kill all the connected users
2. Alter database with rollback options
For SQL 2000 , the recommended option is ALTER database.
---
To make the database immediately single user with all the user disconnected
ALTER database <dbname> set single_user with rollback immediate
To make the database single user after n seconds then ( Here it is 120
seconds)
ALTER database <dbname> set single_user with rollback after 120
Thanks
Hari
MCDBA
"maxz" <anonymous@.discussions.microsoft.com> wrote in message
news:272501c47061$cad93e30$a601280a@.phx.gbl...
> Hi ,
> How does the SINGLE USER actually works ,
> i have used the follwing commands :
> sp_dboption 'acb' , 'single user' 'true' and refresh
> which it shows the the db : abc is now single user.
> However , i can still access that db via odbc.
> Is this how a single user shld works ? Not as wat in
> the Books Online :
> SINGLE_USER allows one user at a time to connect to the
> database. All other user connections are broken. The
> timeframe for breaking the connection is controlled by the
> termination clause of the ALTER DATABASE statement. New
> connection attempts are refused. The database remains in
> SINGLE_USER mode even if the user who set the option logs
> off. At that point, a different user (but only one) can
> connect to the database
>
> thkz
>|||Hi,
you r right i am referring the the single user on
database level.
i have tried from my client pc to have 2 odbc
connection to the same database and it allows me to do
so. is this possible under single user ?
thks & rdgs

>--Original Message--
>Single user means one user. That one user can connect
through any interface,
>including an odbc connection.
>Are you possible confusing single user mode of a database
with single user
>mode of the whole SQL Server. This option you've
described refers to a
>single database. Maybe other connections are to other
databases.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"maxz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:272501c47061$cad93e30$a601280a@.phx.gbl...
the[vbcol=seagreen]
logs[vbcol=seagreen]
>
>.
>|||Hi Maxz,
If you mention "SINGLE_USER with ROLLBACK IMMEDIATE" you cant make 2
connections to sql server.
See my previous post on how to set the single_user at database level.
Thanks
Hari
MCDBA
"maxz" <anonymous@.discussions.microsoft.com> wrote in message
news:270901c47067$f18e3e30$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> you r right i am referring the the single user on
> database level.
> i have tried from my client pc to have 2 odbc
> connection to the same database and it allows me to do
> so. is this possible under single user ?
> thks & rdgs
>
> through any interface,
> with single user
> described refers to a
> databases.
> message
> the
> logs|||Hi ,
i hope i am doing it correct. This is how i do it.
first i opened 2 ms access and connect to the db via
odbc connection
at the server end , i used the query analyzer and
execute the alter database with rollback immediate
i still find that the connections are still there , and
even when i closed the access and re-open i could still
open the links
rdgs
>--Original Message--
>Hi,
>You cannot set the database to single user mode usng the
below comamnd if
>any user is connected to that partcular database.
>sp_dboption 'northwind','single user',true
>There are 2 options to make the database single user:-
>1. Kill all the connected users
>2. Alter database with rollback options
>For SQL 2000 , the recommended option is ALTER database.
>----
--
>To make the database immediately single user with all the
user disconnected
>ALTER database <dbname> set single_user with rollback
immediate
>To make the database single user after n seconds then (
Here it is 120
>seconds)
>ALTER database <dbname> set single_user with rollback
after 120
>Thanks
>Hari
>MCDBA
>
>
>"maxz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:272501c47061$cad93e30$a601280a@.phx.gbl...
the[vbcol=seagreen]
logs[vbcol=seagreen]
>
>.
>|||Hi,
Can you login to Query Analyzer and execute SP_WHO and see whether there are
any connection made to that database (see the dbname column). Because I feel
that you are connecting to a different database , might be "MASTER" database
will be the default dabase when user connects in.
Thanks
Hari
MCDBA
"maxz" <anonymous@.discussions.microsoft.com> wrote in message
news:27c301c4706f$43c9dcc0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi ,
> i hope i am doing it correct. This is how i do it.
> first i opened 2 ms access and connect to the db via
> odbc connection
> at the server end , i used the query analyzer and
> execute the alter database with rollback immediate
> i still find that the connections are still there , and
> even when i closed the access and re-open i could still
> open the links
> rdgs
> below comamnd if
> --
> user disconnected
> immediate
> Here it is 120
> after 120
> message
> the
> logs|||You should verify that you really have gotten into single user mode.
SELECT databasepropertyex('name of db', 'UserAcccess')
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"maxz" <anonymous@.discussions.microsoft.com> wrote in message
news:27c301c4706f$43c9dcc0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi ,
> i hope i am doing it correct. This is how i do it.
> first i opened 2 ms access and connect to the db via
> odbc connection
> at the server end , i used the query analyzer and
> execute the alter database with rollback immediate
> i still find that the connections are still there , and
> even when i closed the access and re-open i could still
> open the links
> rdgs
> below comamnd if
> --
> user disconnected
> immediate
> Here it is 120
> after 120
> message
> the
> logs