Post reply

Issues creating tables with table owner as DBO

  • July 15, 2010 at 10:44 am

    #97395

    Hi All,

    I am a dba in a small firm. A developer has issues creating tables with dbo as schema/owner of that table.

    It is being created with his name as owner like johnf.<>tablename

    so I checked his access and he has Db_owner rights but I dont know why it is being created under his name when he as dbo rights.

    can anyone tell how to fix this issue.

    Lately I am working on sql 2005 and I dont have any kind of practical experience in sql 2000.

  • July 15, 2010 at 11:37 am

    #1193663

    Being a member of the db_owner role isn't the same as being dbo as far as object ownership goes. The owner of the object will default to the user's schema. Have the developer try creating objects as "dbo.<tablename>".

    Greg

  • July 15, 2010 at 11:48 am

    #1193675

    Thank you very much

    With dbo. he was able to create table with table owner as dbo.

    Is there any way to make users as default table owner as dbo ,I mean any option like create user with default schems dbo?

    I have sa rights on server and when I create table without dbo it is still taking dbo. NOT my name.

    So by default users who has sa rights takes dbo ?

  • July 15, 2010 at 12:11 pm

    #1193691

    here's some code that will change every non-dbo schema-ed table to be in the dbo schema again. you'd need to tweak it to cover procs/views/functions, if that is an issue for you as well.

    if there are two tables, ie bob.Invoices and a dbo.Invoices, obviously it will fail because the object already exists:

    declare

    @dbname varchar(500)

    declare c1 cursor for

    select 'EXEC sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'' ,''dbo'' ' from information_schema.tables where table_schema <> 'dbo'

    open c1

    fetch next from c1 into @dbname

    While @@fetch_status <> -1

    begin

    exec (@dbname) --change the objects!

    fetch next from c1 into @dbname

    end

    close c1

    deallocate c1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 15, 2010 at 12:37 pm

    #1193710

    Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

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