Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I strictly limit access to database ?


Can I strictly limit access to database ?

Author
Message
RZ52
RZ52
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 74
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
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
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
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17641 Visits: 32268
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
RZ52
RZ52
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 74
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
RZ52
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 74
Dear Grant,

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

Regards
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search