Preventing access to a database temporarily

[arrow483]

Here's what I am trying to do. We have a job that runs a DTS (this is working). What I want to do is prevent a user group from accessing this db while the job is running.

This is setup under SQL server agent / jobs as a timed job.
Here's what I have so far.

Step 1

DENY SELECT ON frl_entity TO [Frx DesignersLaunchers];

Step 2

DTS …..

Step 2

REVOKE SELECT ON frl_entity TO [Frx DesignersLaunchers];


But frl_entity is a table. I would prefer the entire DB be restricted. Actually, if I could just disable the login temporarily it would be better, but I can't figure how to do that automatically.

 

[SQLBill]

Check out SINGLE_USER mode in the BOL. Specifically, look up ALTER TABLE. The SINGLE_USER option will allow you to put the database in that mode.

One warning. If you have Enterprise Manager open - that is one connection. If you then open Query Analyzer, it will fail as that is two connections. If you try to run a job, that is another connection.

Close all applications, open QA, put database in SINGLE_USER mode, then run the command from QA.

-SQLBill

Posting advice: FAQ481-4875

 

[arrow483]

Thanks. I will have to consider how that would work running as a scheduled job under SQL Server Agent. I am not using QA. But may have some promise.

 

[SQLBill]

Possibility:

Step one of the job....ALTER DATABASE dbname SET SINGLE_USER
Step two of the job....do what you need to do
Step three of the job....ALTER DATABASE dbname SET MULTI_USER

Of course.......test it.

-SQLBill

Posting advice: FAQ481-4875