keeping users off the server 
Author Message
 keeping users off the server

You could set the database to single user mode during the processing and
resetting it after you are done.

Dan



Quote:
> Hello!

> My organization is in the process of converting from a legacy system to
sql
> server.  We are in the process of converting all of our reporting
> functions.  On a daily basis, we convert data from the sql server to the
> legacy system.  I am looking for a way to lock users out of the database
> while the conversion is taking place.  I could use the security features
to
> revoke all of their privileges in the database, but then I would need to
> rebuild the permissions for each table after it is converted.  Is there a
> way to allow users to have rights within a database but to deny them
> permissions to use that database?  Or is there another approach that I
> should take?

> I thank you in advance.

> Victor Ambruso

> --
> Any opinions expressed herein are mine and mine alone.



Tue, 10 Aug 1999 03:00:00 GMT
 keeping users off the server

I had considered this.  There are times during the conversion process
during which there is nobody connected to the server.  During that time, a
user could connect and block the conversion program from continuing.

Thanks for the idea, Dan.  Any other suggestions?

Victor Ambruso

--
Any opinions expressed herein are mine and mine alone.



Quote:
> You could set the database to single user mode during the processing and
> resetting it after you are done.

> Dan



> > Hello!

> > My organization is in the process of converting from a legacy system to
> sql
> > server.  We are in the process of converting all of our reporting
> > functions.  On a daily basis, we convert data from the sql server to
the
> > legacy system.  I am looking for a way to lock users out of the
database
> > while the conversion is taking place.  I could use the security
features
> to
> > revoke all of their privileges in the database, but then I would need
to
> > rebuild the permissions for each table after it is converted.  Is there
a
> > way to allow users to have rights within a database but to deny them
> > permissions to use that database?  Or is there another approach that I
> > should take?

> > I thank you in advance.

> > Victor Ambruso

> > --
> > Any opinions expressed herein are mine and mine alone.



Tue, 10 Aug 1999 03:00:00 GMT
 keeping users off the server

Victor,

If the conversion process is logged in to the database as dbo (perhaps sa
loginid), you could complement sinlge user with 'dbo use only'. This would
perhaps further minimize the risk of anyone "getting in between
operations".

If you are considered just about updates during conversations (desiring a
snapshot), you could write the script so that before "export" for one/all
tables perform this:

begin tran
select * from tbl1 HOLDLOCK
select * from tbl2 HOLDLOCK
etc...
perform export routines
commit tran

This will prohibit updates. Investigate if HOLDLOCK is the best option or
to force a TABLOCKX (note: exclusive but less work for lockmanager to do,
lock escalation).

Another note:
Setting database options will fail if more the one/not_dbo is using the db.
This makes it a bit more "trickier" to perform the job as a batch job. You
would have to extract all connection id (sysprocesses) and KILL them all
(except you own). The transaction/lock aproach would possibly be easier to
code, but you would still have to make sure that you gain the locks,
otherwise your batch operation waits /not get done until the other users
release their locks. This might/might not be a problem to you. This
alternative might lead you to KILL the processes as well.
--

Instructor (MCT, MCSE, MCSD)
Cornerstone Sweden AB



Quote:
> The only other things I can think of is to put the whole server into
single
> user mode (probably not an option if you have other databases out there)
> The other possibility is to just tell the users to stay out during
certain
> times and if they do get in to disconect them when you need to.  Another
> possibility is to rename the database before and after the conversion
(not
> a real pretty prospect).



Tue, 10 Aug 1999 03:00:00 GMT
 keeping users off the server

Hello!

My organization is in the process of converting from a legacy system to sql
server.  We are in the process of converting all of our reporting
functions.  On a daily basis, we convert data from the sql server to the
legacy system.  I am looking for a way to lock users out of the database
while the conversion is taking place.  I could use the security features to
revoke all of their privileges in the database, but then I would need to
rebuild the permissions for each table after it is converted.  Is there a
way to allow users to have rights within a database but to deny them
permissions to use that database?  Or is there another approach that I
should take?

I thank you in advance.

Victor Ambruso

--
Any opinions expressed herein are mine and mine alone.



Tue, 10 Aug 1999 03:00:00 GMT
 keeping users off the server

The only other things I can think of is to put the whole server into single
user mode (probably not an option if you have other databases out there) .
The other possibility is to just tell the users to stay out during certain
times and if they do get in to disconect them when you need to.  Another
possibility is to rename the database before and after the conversion (not
a real pretty prospect).

Dan



Quote:
> I had considered this.  There are times during the conversion process
> during which there is nobody connected to the server.  During that time,
a
> user could connect and block the conversion program from continuing.

> Thanks for the idea, Dan.  Any other suggestions?

> Victor Ambruso

> --
> Any opinions expressed herein are mine and mine alone.



> > You could set the database to single user mode during the processing
and
> > resetting it after you are done.

> > Dan



> > > Hello!

> > > My organization is in the process of converting from a legacy system
to
> > sql
> > > server.  We are in the process of converting all of our reporting
> > > functions.  On a daily basis, we convert data from the sql server to
> the
> > > legacy system.  I am looking for a way to lock users out of the
> database
> > > while the conversion is taking place.  I could use the security
> features
> > to
> > > revoke all of their privileges in the database, but then I would need
> to
> > > rebuild the permissions for each table after it is converted.  Is
there
> a
> > > way to allow users to have rights within a database but to deny them
> > > permissions to use that database?  Or is there another approach that
I
> > > should take?

> > > I thank you in advance.

> > > Victor Ambruso

> > > --
> > > Any opinions expressed herein are mine and mine alone.



Tue, 10 Aug 1999 03:00:00 GMT
 keeping users off the server

It seems to me that once your process is logged on, you could just kill
any other processes if necessary and then pause the server.  This will
prevent further logins until you are done and then resume the server.

Daryl

Quote:

> Hello!

> My organization is in the process of converting from a legacy system to sql
> server.  We are in the process of converting all of our reporting
> functions.  On a daily basis, we convert data from the sql server to the
> legacy system.  I am looking for a way to lock users out of the database
> while the conversion is taking place.  I could use the security features to
> revoke all of their privileges in the database, but then I would need to
> rebuild the permissions for each table after it is converted.  Is there a
> way to allow users to have rights within a database but to deny them
> permissions to use that database?  Or is there another approach that I
> should take?

> I thank you in advance.

> Victor Ambruso

> --
> Any opinions expressed herein are mine and mine alone.



Tue, 10 Aug 1999 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. How to keep user account when import/export in SQL Server 7

2. Kicking users off an Interbase server

3. automatically log off users in SQL Server 2000

4. VFP my toolbars keep falling off !

5. Off load create user to user level?

6. 20% off OCP Exams, 50% off Self Test, 70% off Oracle8i Training, Oracle10G info and more

7. Keeping other users out while COPYing...

8. keeping odbc-users out?

9. users have to keep logging on

10. Keeping users out ...

11. FPW Problem .... Keeping user out of BROWSE fields

12. how to keep users out of isql/w?


 
Powered by phpBB® Forum Software