SQL Server Performance

sql server configuration

Discussion in 'ALL SQL SERVER QUESTIONS' started by Wendy elizabeth, May 12, 2012.

  1. Wendy elizabeth New Member

    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?
  2. Daxesh New Member

    When we install SQL server, 'sa' login is created by default with 'sysadmin' server role.
    Similarly when we create a database, 'dbo' user is created by default for the login who creates the database with 'db_owner' database role.

    'sa' login is created with 'sysadmin' role and you can add another login(s) with same 'sysadmin' role that can perform all the task what 'sa' can.
    Similarly 'dbo' user in database is created with 'db_owner' role and you can add another user(s) that with 'db_owner' role that can perform all the task what 'dbo' user can.

    Usually it does not make any difference, 'dbo' is created for which login. Still I would say not to use any individual's account for 'dbo' that can be disabled in the future

    'dbo' user cannot be dropped, but you can change login associated with 'dbo' using ALTER AUTHORIZATION or SP_CHANGEDBOWNER
    for othe database user you can drop and re-create for different login
  3. Shehap MVP, MCTS, MCITP SQL Server

    From the angle of Microsoft best practices, dbo user should be a user rather sysadmin to reduce some SQL injection risks as for examples privileges assigned for dbo user can judge if you can deploy an external access assembly or not , thereby for sure if dbo has sysadmin privileges , it can definitely deploy such kind of assemblies.

    Hence, it is clear now there is some distinct meaning between that dbo user and any other user having dbo privileges.

    Please let me know if any further help needed
  4. Chililla New Member

    DBO is a schema within the database and this is related to the security within the db, inside SQL you can have schemas to determine wich people can access to the data in the tables for example you can an schema named HR and another named Prod
    and inside these schemas you can have a table named UserID so you will have HR.UserID and Prod.UserID
    but the users granted on HR schema will not be able to see the Prod schema and viceversa
    So the Sysadmin is a Role in the complete SQL server is good to mention that there is another role named db_owner which is very different than the DBO schema so the users added to this role can make any changes in the objects at that specific db

    so do not confuse an schema and a Role they are two different things even though they are related to the data secutity

    when you create a new db you can assigne anyone in the db as db owner which means that that person will have the control of the DB
    SQL normally sets the SA account as DBO and this is because in SQL 2000 it was mandatory to add someone as DB owner so the problem was when that use was deleted the database was not accesible and you have to recreate it
    so now in SQL 2005 and avobe this is optional and as I said SQL makes the user that creates the database as the db owner but that can be changed at anytime

    So comming back to the Security be very carefull which user do you grant access as SA as this is the most power level in the server and the SA role members can do anything at the Instance level

    Hope this can clarify a little bit more your questions

Share This Page