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 recoverability

    We 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 recoverability

    We 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