db owner question

Question

db owner question

paul-144416

SSC Enthusiast

Points: 171
More actions
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

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

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

Darrell Zink SSC Enthusiast Points: 187 More actions · Answer 1

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.

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 2

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.

paul-144416 SSC Enthusiast Points: 171 More actions · Answer 3

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