Re: Keeping users out while updating data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 05/15/04 

Date: Sat, 15 May 2004 15:53:23 +0530

Hi,

I do agree with Jaspers suggestion, set the database to restricted user and
using the same connection (inside query analyzer) try to execute the
procedure

Alter database northwind set RESTRICTED_USER with rollback immediate
go
exec procedures_name
go
Alter database northwind set MULTI_USER

Thanks
Hari
MCDBA

"Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
news:uH2v#LdOEHA.1616@TK2MSFTNGP12.phx.gbl...
> Setting it to restricted_user might be better assuming the normal users do
> not have elevated privileges - with single_user the risk is someone
getting
> the connection before you which sounds like what is happening
>
> --
> HTH
>
> Jasper Smith (SQL Server MVP)
>
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
>
> "M Smith" <msmith@avma.org> wrote in message
> news:eagtzxcOEHA.2780@TK2MSFTNGP09.phx.gbl...
> > OK, I set the database to start up in single_user mode. The problem is
> when
> > I try to open query analyzer to execute my stored proc it won't let me
in.
> > It gives me a log in failure because SQL Server is in single user mode.
> How
> > can I execute my stored proc?
> >
> > Mike
> >
> > "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> > news:e15w6VcOEHA.2344@TK2MSFTNGP10.phx.gbl...
> > > You could set it to single_user temporarily...
> > >
> > > --
> > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.aspfaq.com/
> > >
> > >
> > >
> > >
> > > "M Smith" <msmith@avma.org> wrote in message
> > > news:#tN5xTcOEHA.644@tk2msftngp13.phx.gbl...
> > > > I have a flat file with data that I need to use to update existing
> > records
> > > > in my database with. I wrote a Stored Proc to do this, but I want
to
> > run
> > > it
> > > > when everyone is out of the database. My question is, how do I keep
> > users
> > > > from accessing the database while my update is running? I thought
> about
> > > > taking it offline but BOL said that while it's offline it can not be
> > > > modified. Does this mean the data or the structure or both?
> > > >
> > > > Thanks
> > > > Mike
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Set Restricted_User
    ... Alter Database dbname Set Restricted_User With Rollback Immediate ... it logs off everyone including sysadmins and db_owners. ... > Are you closing the connection after you execute the ...
    (microsoft.public.sqlserver.security)
  • using "USE dbname" before ALTER DATABASE
    ... In the examples for ALTER DATBASE in the SQL Books Online they always ... execute a USE MASTER before the ALTER DATABASE... ... is there a way to use ALTER DATABASE to just *view* various db ...
    (microsoft.public.sqlserver.programming)