Results 1 to 8 of 8

Thread: Database Owner

  1. #1
    Join Date
    Jun 2010
    Posts
    28

    Unanswered: Database Owner

    Hi All

    What is the purpose of a database owner i.e. the dbo.DBNAME that comes before the name of the database?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on where you are seeing this occurrence of dbo. What version of SQL, what tool are you using, and where is the reference appearing?

    It would help if you could post a screen shot for us.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    Hi,

    I've dealt with SQL server for many years now and have not found a purpose of database owner. I think it is a carry over from the old days where a database object is tied to the owner/creator. but today you can tie a database object to to a schema instead of a user/owner. Reason for the move away from owner -> schema. If the user account is removed/disabled in windows or active directory, then the database object becomes orphaned. So no reason to tie a database object to a user account.

  4. #4
    Join Date
    Jun 2010
    Posts
    28
    Hi All

    Many thanks for your replies.

    Everytime I create a database - the tables begins with dbo.[database name].

    but surely there must be a logical reason for Microsoft to include this otherwise they could just easily take it out of SQL Server?

    Thanks

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've seen many tools that use the full three part name which will identify any object on a SQL Server. This would be like master.dbo.sysprocesses or tempdb.dbo.sysobjects which are present on every SQL Server.

    So far, I've never seen any tool that presents objects as dbo.[model].sysusers because as far as I know, none of the standard Microsoft supplied tools could correctly interpret that name.

    What tool are you using?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2010
    Posts
    28
    Hi Pat - im using SQL Server 2008. My question really comes from dragging and dropping an object i.e. a table into my query pane in sql server 2008 - it prefixese it with a
    Attached Thumbnails Attached Thumbnails DBO Pic.png  

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Microsoft tools use the three part names to make queries more dependable for new users. For a quick demo of the "why", run the following script:
    Code:
    USE [master]
    GO
    
    CREATE DATABASE Shamas21a
    CREATE DATABASE Shamas21b
    GO
    
    CREATE TABLE Shamas21a.dbo.c (
       myColumn     CHAR(10)        NOT NULL
       PRIMARY KEY (myColumn)
       )
    
    CREATE TABLE Shamas21b.dbo.d (
       myColumn     DATETIME        NOT NULL
       PRIMARY KEY (myColumn)
       )
    GO
    
    INSERT INTO Shamas21a.dbo.c (myColumn) VALUES ('It works!')
    INSERT INTO Shamas21b.dbo.d (myColumn) VALUES ('2000-01-02 03:45:32.1')
    
    SELECT Db_Name() AS 'Database Name'
    GO
    SELECT * FROM Shamas21a.dbo.c
    GO
    SELECT * FROM Shamas21b.dbo.d
    GO
    SELECT * FROM c
    GO
    SELECT * FROM d
    GO
    
    USE [Shamas21a]
    GO
    SELECT Db_Name() AS 'Database Name'
    GO
    SELECT * FROM Shamas21a.dbo.c
    GO
    SELECT * FROM Shamas21b.dbo.d
    GO
    SELECT * FROM c
    GO
    SELECT * FROM d
    GO
    
    USE [Shamas21b]
    GO
    SELECT Db_Name() AS 'Database Name'
    GO
    SELECT * FROM Shamas21a.dbo.c
    GO
    SELECT * FROM Shamas21b.dbo.d
    GO
    SELECT * FROM c
    GO
    SELECT * FROM d
    GO
    
    USE [master]
    GO
    
    DROP DATABASE Shamas21a
    DROP DATABASE Shamas21b
    GO
    It is a lot easer to read the script output if you execute it in "text mode". You can enable this by typing a Ctrl-T in the query window or by selecting the "Query | Results to | Results to text" menu option (see screen shot).

    This script creates two databases and puts a table in each of them. It then shows a couple of scenarios where the same statements succeed/fail in different ways depending on what database is in use at the time. Note that the three part names work in every scenario.

    -PatP
    Attached Thumbnails Attached Thumbnails 201312301128.jpg  
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2010
    Posts
    28
    Thanks Pat - it makes sence now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •