Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I strictly limit access to database ? Expand / Collapse
Author
Message
Posted Sunday, March 31, 2013 1:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
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
Post #1437319
Posted Sunday, March 31, 2013 6:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
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
Post #1437340
Posted Monday, April 1, 2013 4:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1437388
Posted Monday, April 1, 2013 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
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
Post #1437475
Posted Monday, April 1, 2013 9:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, Visits: 74
Dear Grant,

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

Regards
Post #1437477
Posted Monday, April 1, 2013 9:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
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
Post #1437479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse