Post reply

db owner question

  • February 18, 2005 at 6:56 am

    #362032

    hello,

     

    i am quite new to Sql Server, here is my problem :

    when i create an object (table,view,sp) in a database on our server, the owner of this object is my login name.

    but i would like it to be dbo by default. this is because we changed to active directory and my login name has changed, so i can't change the objects i created with my old login. so two issues here

    create objects with dbo as owner

    transfer ownership from old to new account

     

    thanks in advance

    paul

  • February 18, 2005 at 8:24 am

    #543089

    Any db object created by a member of the systemadministrators server role shows dbo as the owner by default.  A member of the systemadministrators server role may also change the ownership of any db object.  Books Online contains other information regarding object ownership.

  • February 18, 2005 at 8:26 am

    #543090

    Issue number 2 is easier to answer first: sp_changeobjectowner 

    SELECT 'EXEC sp_changeobjectowner @objname = ' + 
       CHAR(39) + TABLE_NAME + CHAR(39) + 
       ', @newowner = ' + CHAR(39) + TABLE_SCHEMA + CHAR(39) +
       CHAR(13) + 'GO'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA  'dbo'

    The above will write your script for you.

    As for creating objects with dbo as owner, I know there is a way, but the only gaurenteed way that I know of is to go with the FQN (DBName.dbo.TableName).

    Have fun....it's only a job, not an adventure.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • February 21, 2005 at 3:03 am

    #543284

    jim

     

    thanks for the script !

    i found the answer in the books online : i need to be a sysadmin to create tables with dbo.

     

    thanks

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

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