Global access ON/OFF switch

Question

Global access ON/OFF switch

odeonkreel

Mr or Mrs. 500

Points: 547
More actions
June 24, 2011 at 7:30 am

#389835

I have many users setup in my SQL 2008 db.
All those users are then assigned to Database Roles I created, based on jib function/department.
Those Roles then specify what tables/views they can SELECT.
But is there a single switch that I can DENYREAD to all users and roles, except the sysadmin server role, do some table maintanence, and then switch the roles back to SELECT?
Currently, I go into each Database Role, and for each Securable I uncheck Select-Grant and check Select-Deny. That turns into quite a few check boxes. Then I reverse the process when I want to let them all in.
Is there a quicker, simpler method? I have a feeling I making this more complicated than needed.

Viewing 4 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

odeonkreel (6/24/2011)
I have many users setup in my SQL 2008 db.
All those users are then assigned to Database Roles I created, based on jib function/department.
Those Roles then specify what tables/views they can SELECT.
But is there a single switch that I can DENYREAD to all users and roles, except the sysadmin server role, do some table maintanence, and then switch the roles back to SELECT?

yes,, a database can be set to modes like RESTRICTED_USER (sysadmins) or SINGLE_USER, or you can even set the whole database to read only;

this will kick everyone off, rollback their transacitons, and only allow admins:

ALTER DATABASE MyDATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

so you could kick everyone off, make your changes, and then set the mode back

ALTER DATABASE MyDATABASE SET MULTI_USER

Currently, I go into each Database Role, and for each Securable I uncheck Select-Grant and check Select-Deny. That turns into quite a few check boxes. Then I reverse the process when I want to let them all in.
Is there a quicker, simpler method? I have a feeling I making this more complicated than needed.

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!

Ant-Green SSC Guru Points: 113563 More actions · Answer 2

yes,, a database can be set to modes like RESTRICTED_USER (sysadmins) or SINGLE_USER, or you can even set the whole database to read only;

restricted_user doesn't just allow sysadmins, it also allows anyone who has db_owner on the database or dbcreator at the server access to the database while the database is set to restricted_user.

From MSDN

SINGLE_USER | RESTRICTED_USER | MULTI_USER

Controls which users may access the database. When SINGLE_USER is specified, only one user at a time can access the database. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. MULTI_USER returns the database to its normal operating state.

odeonkreel Mr or Mrs. 500 Points: 547 More actions · Answer 3

Perfect.

That will work exactly like I need it to. None of the users are owners of anything, all I allow them to do is run Select statements on views I created for them.