Object Owner - mapping to dbo

Question

Object Owner - mapping to dbo

rodjkidd

SSCoach

Points: 15883
More actions
June 6, 2006 at 4:17 am

#171069

Hi All,
We are currently using SQL Server 2000. I don't have full admin rights as we are "borrowing" and instance until our own kit turns up.
I have been granted dbo rights to the database we are using. I can add memebers of the dev to the dbo database role.
The problem is one of the dev team needs to be able to create tables. I have granted him permission to do this, but he can't name them with dbo as the owner. We would rather have all the tables in the project with dbo as the owner.
Are the only ways I can achive this:
1. Add him to the dbo fixed role and end up giving him more access that I want. He's fairly new to SQL Server! So baby steps.
2. Use sp_changedbowner on each table he creates
Or is there another way I can achive the same result?

Many thanks,
Rodney.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Rodney

I take it you have added him the the db_ddladmin database role? All you need to do is have him create tables as follows:

create table dbo.NewTable (<column spec&gt

John

rodjkidd SSCoach Points: 15883 More actions · Answer 2

Hi John,

Thanks for that, but doesn't that also allow him to drop objects?

Cheers,

Rodney.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Rodney

That's right. If you don't want that, then the only thing I can think of is to create a job that checks for objects not owned by dbo and changes them to dbo ownership. Schedule the job to run at whatever interval you think is appropriate.

I have actually implemented something like this myself where a third-party application creates objects on the fly. I run the job overnight so that everything is owned by dbo in the morning. I can post the scipt for you if it would help.

The other option is to upgrade to SQL 2005, which, I believe, allows you to create DDL triggers.

John

rodjkidd SSCoach Points: 15883 More actions · Answer 4

Hi John,

Many thanks for that.

The funny thing is he'll end up with full admin rights eventually but this is his first moving from Access to SQL Server, so I was hoping to do it in baby steps.

So I may as well add him to the dbo role.

We should be moving over to 2005 but I don't know when. We shoudl be getting our own set of servers, with me as one of the admins hopefully, but as our company just merged with two others all capital spends are on hold!

I like th idea with the jobs, but I am not allowed to create jobs on the server we are piggybacking on! Doh!

Cheers,

Rodney.