Can I strictly limit access to database ?

Question

Can I strictly limit access to database ?

RZ52

Mr or Mrs. 500

Points: 536
More actions
March 31, 2013 at 1:07 pm

#275794

Hi,
I'm new in SQL Server administration however I need to make some major modifications in my SQL Server database. There are several applications from different places access tables. I've already called them not to use apps during process but I need to be sure nothing happens by mistake.
So, I want to know if there is an option to restrict any access from outside during my work except my access. As I also develop a user interface by VS, I will need to have access through my VS too (Visual Studio is installed on the SQL Server computer so it will be a local access).
Thanks in advance for helps

Viewing 6 posts - 1 through 5 (of 5 total)

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

You can put any of the user databases on the box into single user mode.

Why would you be developing against a production database? If needed, create a database on the same box, or install another instance on the box. You should not be developing on the same database that is being used as a production database.

Is this what you mean? I am not totally clear as to what you actually need.

Andrew SQLDBA

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

It does depend on how your security is set up, but you can set a database to restricted_user. This will make it so that only logins with privileges as SA or db owner can access it. But, if your regular logins are either of these, they'll still be able to get in. Here's how you do it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RZ52 Mr or Mrs. 500 Points: 536 More actions · Answer 3

AndrewSQLDBA (3/31/2013)
You can put any of the user databases on the box into single user mode.
Why would you be developing against a production database? If needed, create a database on the same box, or install another instance on the box. You should not be developing on the same database that is being used as a production database.
Is this what you mean? I am not totally clear as to what you actually need.
Andrew SQLDBA

Dear Andrew,

Thanks for advice. The system I'm working on is an old database and now the owner asked me to add some more tables, making new joins, etc based on their new needs so I know it is not a proper situation.

To do my job, I need to work both on user interface app and database and it might take time so I want to be sure only I can connect to database to avoid disintegration of data.

Any suggestion will be appreciated.

Regards

RZ52 Mr or Mrs. 500 Points: 536 More actions · Answer 4

Dear Grant,

Thanks for your help. I will check the login profiles and their privileges.

Regards

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

You should create another database and use that for all your development work. Set up a sql job that performs a backup and restores it over your development database. How long do you expect it to take to do the development?

Andrew SQLDBA