Revoke Access to Groups

Question

Post reply

Revoke Access to Groups

Mike Levan

SSChampion

Points: 14201
More actions
March 3, 2009 at 4:31 pm

#197663

I will be loading heavy data each week which takes around 5 hrs and i would like to keep all the developers out of that database during my load so that other users will not interact with my load which may cause for slowing down my process. what is the best way to keep them away for few hrs.
I thought this way.
1. drop user from Database
2. Load Data
3. Add User to the database

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i'd just set the database to single user mode, do my work, and then change it back to multi user;

no fiddling with groups, and you'll know you are the only one that can connect anyway.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike Levan SSChampion Points: 14201 More actions · Answer 2

But how could i setup this within a job. I want this to be done by the developers who does bulk data load.

For droping the user i thought of creating a store proc with exec as 'dbo' so that developer can drop the user before the load.

Lowell SSC Guru Points: 323518 More actions · Answer 3

it'd be something like this:

--kick everyone off no matter what!

ALTER DATABASE [YOURDATABASENAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

--make sure it's really just for me

ALTER DATABASE [YOURDATABASENAME] SET SINGLE_USER

GO

--do work

GO

--let others connect again

ALTER DATABASE [YOURDATABASENAME] SET MULTI_USER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike Levan SSChampion Points: 14201 More actions · Answer 4

yeah i tried this but if a developer wants to run this from a sql agent job, how he is going to select database instantly.

Also when i tried dropping user from the database and adding him, i may also need them to the db_role which can be done with sp_addrole but i dont think developers can exec that system procedure.

thanks

Mike Levan SSChampion Points: 14201 More actions · Answer 5

Also I expereinced in some situtaitons where database will not come out of single user mode, thats a danger for me just in case.

thanks

Mike Levan SSChampion Points: 14201 More actions · Answer 6

Can i do something like this..

I am getting syntax error here.

ALTER PROCEDURE [dbo].[SingleMode]
@DB as varchar(15)
WITH EXECUTE AS 'dbo'
AS
BEGIN
SET NOCOUNT ON;
ALTER DATABASE ' + @DB + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE ' + @DB + ' SET SINGLE_USER ;
END

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

might be a PITA, but set all dev jobs to run as a special user account you create

create a trigger to drop all connections that aren't from sa or another admin domain account, and disable the trigger or don't create it in the db

create the trigger in the db or enable it

run your load under sa

disable or drop the trigger