locked
How do I protect my database RRS feed

  • Question

  • My database is in a public machine, and I do not want anybody touch my database, how do I get that? I know the limitation of permission, is there any different way?

    Wednesday, December 14, 2011 4:12 PM

Answers

  • One more method

    However, this works perfectly if the user is the owner of the database. Look 

    that …

     

    • Create a new SQL login "login1"

    • Create a user named “login1” in master database

    • Grant CREATE DATABASE to login1

    • While impersonating login1, create a database called “dbteste”

    • Revoke CREATE DATABASE permission from login1

    • Revoke VIEW ANY DATABASE permission from PUBLIC

    • Register this server as login1

    • From the “login1” session, expand database tree. Now, you should see 

    master, tempdb, dbteste

    • Grant VIEW ANY DATABASE to PUBLIC

    • From the “login1” session, you should see all the databases


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by alfred chou Monday, December 19, 2011 12:30 PM
    Monday, December 19, 2011 11:38 AM
    Answerer

All replies

  • Hey

    for this type  situation remove the Built\admin and give a good password for sa.

    as usual we know about limitation permisson for database user.

    I requisting other experts "is my recomendations are valid ?

     


    SNIVAS
    Wednesday, December 14, 2011 4:28 PM
  • also check this article http://www.sql-server-citation.com/2009/05/how-to-secure-sql-server-sql-server.html

    http://uk.linkedin.com/in/ramjaddu
    Wednesday, December 14, 2011 4:31 PM
  • Hello Alfred,

    SQL Server permissions are a proper methode to protect you database from prohibited access. What more on security do you expect?

    You could additional encrypt your data.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Wednesday, December 14, 2011 4:32 PM
  • - Give a strong SA.

    - Change the default port.

    - Validate who all has sysadmin and ensure only proper users has it.

    -BuiltIN admins was there by default only in SQL Server 2005.It was removed on 2008/above.

    And as mentioned by Olaf,you can work with encryption is its really required.

     

     


    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Wednesday, December 14, 2011 10:21 PM
  • try this code
    CREATE DATABASE "secure.sdf"
    DATABASEPASSWORD '<enterStrongPasswordHere>'
    else do it manually
    1. Open the "SQL Server Enterprise Manager". This is usually under "Start"-->"Programs"-->"Microsoft SQL Server".

    2. Navigate to the "Logins" object under the "Security" folder on the SQL Server you wish to administer. Then, right click on the 'sa' account and select "Properties".

    3. Now, enter a new password in the "Password" field under the "Authentication" options.
    Database consultant NY, USA - Intelcs.com
    Monday, December 19, 2011 11:21 AM
  • One more method

    However, this works perfectly if the user is the owner of the database. Look 

    that …

     

    • Create a new SQL login "login1"

    • Create a user named “login1” in master database

    • Grant CREATE DATABASE to login1

    • While impersonating login1, create a database called “dbteste”

    • Revoke CREATE DATABASE permission from login1

    • Revoke VIEW ANY DATABASE permission from PUBLIC

    • Register this server as login1

    • From the “login1” session, expand database tree. Now, you should see 

    master, tempdb, dbteste

    • Grant VIEW ANY DATABASE to PUBLIC

    • From the “login1” session, you should see all the databases


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by alfred chou Monday, December 19, 2011 12:30 PM
    Monday, December 19, 2011 11:38 AM
    Answerer
  • Thanks, Uri
    Monday, December 19, 2011 12:31 PM