Re: How can I open an SQL database and be the only one who has access to it?
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 10/02/04
- Next message: Val Mazur: "Re: Stream object and 'Invalid character' error"
- Previous message: William Hildebrand: "Re: How can I open an SQL database and be the only one who has access to it?"
- In reply to: William Hildebrand: "Re: How can I open an SQL database and be the only one who has access to it?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 1 Oct 2004 18:21:09 -0700
Using SET options, you can determine how SS permits access to your server.
SINGLE_USER | RESTRICTED_USER | MULTI_USER
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.
To allow multiple connections, the database must be changed to
RESTRICTED_USER or MULTI_USER mode.
RESTRICTED_USER allows only members of the db_owner fixed database role and
dbcreator and sysadmin fixed server roles to connect to the database, but it
does not limit their number. Users who are not members of these roles are
disconnected in the timeframe specified by the termination clause of the
ALTER DATABASE statement. Moreover, new connection attempts by unqualified
users are refused.
MULTI_USER allows all users with the appropriate permissions to connect to
the database. MULTI_USER is the default setting.
The status of this option can be determined by examining the UserAccess
property of the DATABASEPROPERTYEX function.
WITH <termination>
The termination clause of the ALTER DATABASE statement specifies how to
terminate incomplete transactions when the database is to be transitioned
from one state to another. Transactions are terminated by breaking their
connections to the database. If the termination clause is omitted, the ALTER
DATABASE statement waits indefinitely, until the transactions commit or roll
back on their own.
ROLLBACK AFTER integer [SECONDS]
ROLLBACK AFTER integer SECONDS waits for the specified number of seconds
and then breaks unqualified connections. Incomplete transactions are rolled
back. When the transition is to SINGLE_USER mode, unqualified connections
are all connections except the one issuing the ALTER DATABASE statement.
When the transition is to RESTRICTED_USER mode, unqualified connections are
connections for users who are not members of the db_owner fixed database
role and dbcreator and sysadmin fixed server roles.
ROLLBACK IMMEDIATE
ROLLBACK IMMEDIATE breaks unqualified connections immediately. All
incomplete transactions are rolled back. Unqualified connections are the
same as those described for ROLLBACK AFTER integer SECONDS.
NO_WAIT
NO_WAIT checks for connections before attempting to change the database
state and causes the ALTER DATABASE statement to fail if certain connections
exist. When the transition is to SINGLE_USER mode, the ALTER DATABASE
statement fails if any other connections exist. When the transition is to
RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified
connections exist.
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "William Hildebrand" <wahilde@cableone.net> wrote in message news:10lrtoopaqsnufc@corp.supernews.com... > Hi Bill, > > I'm not sure what that means, but it sounds like you are talking about > getting exclusive rights to the entire SQL server whereas all I want is > exclusive rights to a single database within the SQL server. > > I'm using a fileDSN, so I just connect by setting the connection string to > "FileDSN=myfiledsn". > After the connection is open the connection property is the following: > > Provider=MSDASQL.1;Extended Properties="DRIVER=SQL > Server;SERVER=SYS-WAHILDE;UID=William Hildebrand;APP=Microsoft Data Access > Components;WSID=SYS-WAHILDE;DATABASE=CMServer;Network=DBMSSOCN;Trusted_Connection=Yes" > > What do I need to change in the connection property to get exclusive > rights to the CMServer database? > > Thanks, > > William Hildebrand > > > "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message > news:%23shuPd%23pEHA.2236@TK2MSFTNGP09.phx.gbl... >> That's a JET switch that's a NOOP with SQL Server. You can ask for a >> single-user mode with DMO or through startup parameters. >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> >> "William Hildebrand" <wahilde@cableone.net> wrote in message >> news:10lr3ectpa44e2c@corp.supernews.com... >>> Hello, >>> >>> I'm opening an ADODB connection to an SQL database in VB6 using the >>> adModeShareExclusive mode for the connection object. The connection >>> opens >>> successfully, but I'm still able to modify records in the database using >>> Enterprise Manager. Why is this? I want to open the SQL database and >>> lock >>> out all other connections, i.e. anyone who tries to connect to it after >>> I >>> have a successful adModeShareExclusive mode connection established >>> should >>> get an error when they try to connect. At least that's what I'm trying >>> to >>> accomplish so that I can do a complete rebuild of the database. I don't >>> want to kick everyone out of their connections when I try to connect. >>> I'll >>> just keep trying until I finally get a connection when no one else is >>> connected. Does anyone out there know how I can do this? Maybe I >>> should >>> simply be using a different mode, but which one? >>> >>> Thanks, >>> >>> William Hildebrand >>> >>> >> >> > >
- Next message: Val Mazur: "Re: Stream object and 'Invalid character' error"
- Previous message: William Hildebrand: "Re: How can I open an SQL database and be the only one who has access to it?"
- In reply to: William Hildebrand: "Re: How can I open an SQL database and be the only one who has access to it?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
