February 13, 2008 at 1:27 pm
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.
February 13, 2008 at 1:31 pm
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
February 13, 2008 at 1:55 pm
If restricted user is the same thing as setting the database single user to true then this is what I want. Thanks!
February 13, 2008 at 3:09 pm
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
February 13, 2008 at 3:42 pm
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.
February 13, 2008 at 9:51 pm
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
February 14, 2008 at 6:19 am
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.
February 14, 2008 at 3:40 pm
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
February 14, 2008 at 9:28 pm
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
February 15, 2008 at 7:30 am
Another way to 'hide' a server and it's databases from the general user is to change the port it is listening on.
February 15, 2008 at 4:54 pm
You could remove all the users except you from the database so no one but you can access it.
February 15, 2008 at 7:58 pm
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