how to set database so no one can access it

Question

Post reply

how to set database so no one can access it

Denise McMillan

SSCertifiable

Points: 6216
More actions
February 13, 2008 at 1:27 pm

#186759

How can I set a database so that no one can log into it? Single user mode would allow one person to log in right? Is there a wa to set it so only sa can log in? Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Restricted user means that only members of the sysadmin server role or db_owner database rolw can connect to the DB.

Is that what you're looking for?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

If restricted user is the same thing as setting the database single user to true then this is what I want. Thanks!

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Single_user access is not the same as restricted_access. As Gail said, restricted_access allows only members of the db_owner sysadmin roles to connect to the database. Single_user allows only one user connection to the database regardless of role membership.

See "Alter Database" in BOL for a description.

Greg

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 4

We have moved this database to a new server. Now I don't want anyone to use the one on the old server. Could I take the database offline? I don't think either single user mode or restricted option is good enough. Thanks again for all ideas.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 5

Could you please be more specific about what you want to do with the database. Do you need to access it or not?

My blog

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

If you want no one to access the DB, sysadmin or otherwise, take the DB offline

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 7

I did take it offline. We probably won't be using it anymore, the users are accessing a copy of it that I restored on the new server now. Thanks so much for all the help.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

Bring the database offline. And when you need it back bring it online and hence use it.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

Another option is to detach it. The only reason I mention it is some applications which look for databases, such as Microsoft CRM (oh, joy), may error out when it scans the list of databases on a SQL Server and comes across one or more databases which are marked offline.

K. Brian Kelley
@kbriankelley

Wallace Wood Mr or Mrs. 500 Points: 513 More actions · Answer 10

Another way to 'hide' a server and it's databases from the general user is to change the port it is listening on.

JeremyE SSCoach Points: 16199 More actions · Answer 11

You could remove all the users except you from the database so no one but you can access it.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Wallace Wood (2/15/2008)
Another way to 'hide' a server and it's databases from the general user is to change the port it is listening on.

If you want to try this- you have to remember to turn off the SQL browser service (which sits around telling everyone else which custom port you're now using...:))

Nothing beats detaching the database - as brian has already mentioned.

Best Defense: no be there (Pat Morita in the Karate Kid)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?