Post reply

Not allowing Restore of Sql Server7 Database

  • May 26, 2003 at 2:26 am

    #81916

    Hi,

    I am using SqlServer 7 where the owner of the database is dbo (mapped to sa).

    I am aware that using that option allows anybody to take a backup of my database

    of the site and restore it on their computer.

    I then changed the owner to a user called mehulshah and then backed up the

    database - then i tried to restore it - and i am able to restore it on another computer

    when i am logged in as 'sa' - when it should not allow me to restore - i.e that right

    should default to 'mehulshah' who is the new database owner.

    If it was MS Access you have have a database password and/or user level

    security which gives some security protecting both your schema and data.

    B/c even though you have access to the physical file - when u try to open it-

    their will be a prompt - before you can go through the data.

    where am i going wrong.

    thanks,

    mehul

  • May 26, 2003 at 5:25 am

    #458489

    Hi mehul,

    quote:

    I then changed the owner to a user called mehulshah and then backed up the

    database - then i tried to restore it - and i am able to restore it on another computer when i am logged in as 'sa' - when it should not allow me to restore - i.e that right should default to 'mehulshah' who is the new database owner.

    I'm not sure if I understand what you write.

    Are you wondering about why the 'sa' can restore the db, because the user 'mehulshah' is owner of the db?

    If so, note that the sa or members of the sysadmin group can virtually do everything within SQL Server, even if he/they is/are not explicit owner of an object. If you don't trust your sa, you will have a problem

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 5, 2003 at 2:36 am

    #459876

    Yes that what i am wondering. Basically i have developed an in house app

    and am planning to deploy the application to one of branches - but i dont

    wont anybody to be able to take the copy of the backup (of the Db) - out of

    site and restore it - as it contains confidential data.

    I had earlier used Access2K and b/c User Level Security and the password

    requirement. I am aware that both can be cracked - but at least not by the

    casual user.

    So even if they tried going through the backup data - they would be prompted

    for a username and password. Anyway to achieve that with SqlServ backup?

    Any suggestions?

  • June 5, 2003 at 9:59 am

    #459998

    You can never block sa access, change the sa password and remove the Built-In Admin Acct. By doing so you can block the rest of the users. Also make sure no else is in the admin role. Perhaps move your database to another server, if your server hosts multi databases

    Shas3

  • June 5, 2003 at 10:13 am

    #460003

    Shas3 is correct. Consider that as far as SQL Server is concerned - SA is GOD. GOD can do anything, you can not stop GOD. But you CAN stop other people/users from pretending to be GOD. Put a strong password on the SA account and don't share it with those who don't need it. Remove the Builtin/Administrator account after testing to ensure that nothing uses it. That account allows anyone who is Administrator for the Operating System to also be Administrator for the SQL Server. Don't put anyone in the SysAdmin Role unless they really require it. Since you appear to be the database administrator, you should be in this Role and not be using the SA account. Also, don't give anyone db_databasebackup privileges unless they really need it.

    -SQLBill

  • June 5, 2003 at 10:45 am

    #460010

    What I understand you want to protect the database backup be restored to somewhere. Try backup database with password option. Sets the password for the backup set. If a password is defined for the backup set, the password must be supplied to perform any restore operation from the backup set.

  • June 7, 2003 at 2:22 am

    #460284

    I have done as Shas3 has suggested but you reply does not address my issue regarding a rogue user taking the backup offsite and restoring to his pc and going through that data. Remember on his pc he will be the 'sa'.

    Regarding Allen Cui's reply I cann't find a option to insert a password when i am backing up the DB. Pls tell me where it is.

    Rgs,

    Mehul

  • June 8, 2003 at 11:20 pm

    #460295

    Sounds like your app will have to do some encryption before writing the data to the database to prevent anyone to fiddle.

  • June 9, 2003 at 3:14 am

    #460308

    It is easy to forget about simple because it is not part of the EM functionality (beats me why and if there is an option to enable somewhere). But with TSQL "Backup Database" the option is

    PASSWORD = { password | @password_variable }

    Sets the password for the backup set. PASSWORD is a character string. If a password is defined for the backup set, the password must be supplied to perform any restore operation from the backup set.

    See SQL BOL "Backup"

    There is also a MEDIAPASSWORD option

    Edited by - antares686 on 06/09/2003 03:14:53 AM

Viewing 9 posts - 1 through 8 (of 8 total)

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