how to set database so no one can access it
-
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If restricted user is the same thing as setting the database single user to true then this is what I want. Thanks!
-
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
-
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.
-
Could you please be more specific about what you want to do with the database. Do you need to access it or not?
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
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 -
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 -
Another way to 'hide' a server and it's databases from the general user is to change the port it is listening on.
-
You could remove all the users except you from the database so no one but you can access it.
-
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?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply