Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
configuratiuon for new database.

已答复 configuratiuon for new database.

  • Saturday, May 12, 2012 2:53 PM
     
     
    Sign In to Vote
    0

    My goal is to setup a new database on an existing instance of sql server 2008 r2 database. I do not want to use the default values, but want to use the preferred recommended values for this new database. thus I have the following questions:

    1. The system assigns the person who is setting up the database as the dbo. However, can you tell me who the dbo should really be? Should the dbo really be the sa (system administrator)?

    2. When you setup a database, there is a dbo 'person' that is assigned but you also have roles the dbo owner. Thus can you tell me and/or point me to a reference that will tell me what the difference is between the dbo of the database and the dbo owner role? Also how is the dbo of the database the same as the dbo owner role?

    3. It looks like I can let the default value of the database set me as as dbo. However it looks like I can assign myself to other user names that are unique within the database while still use the same domain\user name to login in. Is this a good idea to allow to occcur? Can you also give me an explaination for your reason?

     

All Replies

  • Saturday, May 12, 2012 7:05 PM
     
     Answered
    Sign In to Vote
    0

    1) It's common in many shops to let sa be the owner of all databases. I am however, not sure that this is best practice. I would suggest that the best is that for every database on the server, you create an SQL login which you assigns as the owner of the database. This SQL login is not granted any access rights on server level, but you should explicitly DENY CONNECT SQL to this login, so no one can log in as that login.

    The reason you don't want a login who is tied to a person to be the owner of the database is that that person may leave the company. Therefore you want an impersonal login. And, as I said, many shops find sa to be a good choice for this. However, this has some security implications, if you later decide to make the database trustworthy. There is all reason to avoid this, but sometimes this is necessary. Casual use of trustworthy can open for privlege elevation. By having a non-priviledge login as owner, there is an extra safeguard.

    I should add that the scheme I outline here, implies some problems when you restore a copy of the datbase to a different server; in this case you need to be careful to change the owner of the database after the restore. If you use sa "this just works".

    2) The difference is between dbo and a user who is db_owner is this. If you your login is Domain\Wendy and you are user in the db_owner role in a database, your user name in the database will also be Domain\Wendy. If you are the owner of the database, you user name is dbo. In both cases, you have the same set of permissions in the database. The one thing that is important is that you set up you user to have dbo as the default schema. (But this is also the default when you create a new user with CREATE USER.)

    Note that logins that are member of the server role sysadmin always map to dbo on database, even if they have a personal user in that database.

    3) Not sure what your question is, but it follows from what I've said above that you should change the owner of the database, and then assign a user for yourself in the database. However, if you are member of sysadmin, you will be dbo nevertheless.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se