Database level lock

Hi,
How do I lock entire database? I want an exclusive lock on the db by a user
who is the dbo of that database only (not sa user).
Thanks,
TanmayaHmm, take a look at
ALTER DATABASE ...SET command along with SINGLE_USER | RESTRICTED_USER |
MULTI_USER
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:%23bdnwHstGHA.3264@.TK2MSFTNGP03.phx.gbl...
> Hi,
> How do I lock entire database? I want an exclusive lock on the db by a
> user who is the dbo of that database only (not sa user).
> Thanks,
> Tanmaya
>|||If I understand you correctly, you could do the following to limit the datab
ase to dbo (and sa) roles only.
ALTER DATABASE MyDatabase
SET RESTRICTED_USER
And then when finished, return to regular use.
ALTER DATABASE MyDatabase
SET MULTI_USER
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message news:%23bdnwHstGHA.3
264@.TK2MSFTNGP03.phx.gbl...
> Hi,
>
> How do I lock entire database? I want an exclusive lock on the db by a use
r
> who is the dbo of that database only (not sa user).
>
> Thanks,
> Tanmaya
>
>|||Actually, the scenario is we have a web application and each week we need to
do data uploads (with etl). During this upload, the users accessing the
website should not be able to read data. This is why I want database lock.
Now the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So will
db locking help in this case as the website can also read the data being a
abc user?
What can be done here?|||How do you do data upload? DTS,Bulk Insert what?
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:elzYtFttGHA.3568@.TK2MSFTNGP05.phx.gbl...
> Actually, the scenario is we have a web application and each week we need
> to do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So
> will db locking help in this case as the website can also read the data
> being a abc user?
> What can be done here?
>|||Tanmaya Kulkarni wrote:
> Actually, the scenario is we have a web application and each week we need
to
> do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So wil
l
> db locking help in this case as the website can also read the data being a
> abc user?
> What can be done here?
>
One option would be to have the web app connect as user 'abc', and the
ETL process as user 'xyz'. When starting the ETL process, revoke access
from user 'abc', and restore it after ETL finishes.
Another option would be to create a new database, do the ETL into that
database, then detach/reattach the "new" database as the "live" database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com