DBO vs. as owner

Question

DBO vs. as owner

Barkingdog

SSCoach

Points: 18670
More actions
November 30, 2001 at 5:19 pm

#78127

I created a Sql 7 test database for one of our developers. I made him dbo-equivalent for the database. When he creates a stored procedures (say "sp_doit") it lists him, not dbo, as the owner. His Sql code has lines like... " EXEC sp_doit" and the command
fails because it can't find the stored procedure.
1. I thought that "EXEC sp_doit" is equivalent to "EXEC dbo.sp_doit." He is dbo equivalent. Why doesn't it work?
2. For the moment, I execute "sp_changeobjectowner <username>.sp_doit', 'dbo'" and his code works. But I don’t want to do this routinely. Suggestion appreciated.
Bill

Viewing 15 posts - 1 through 15 (of 17 total)

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

You'll have to "alias" him as dbo, not just give him dbo level access. You're right about exec, but it's really doing 'exec hislogin.objectname'. To do this, you need to remove the user from the db, then run this:

EXEC sp_addalias 'his login', 'dbo'

He'll have dbo access and all objects will be owned by dbo. When work is done, you can remove it with sp_dropalias.

The other alternative is to make him the actual db owner. I normally keep all mine owned by SA, but it's convienient in some cases. Just use sp_changedbowner to do it. After that he IS the dbo.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Wei Cao SSC Veteran Points: 261 More actions · Answer 2

Andy,

Does the statement:

EXEC sp_addalias 'his login', 'dbo'

only work for SQL 7 server not SQL 2000?

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

Should workin both. Are you getting an error? Perhaps he still has a login in the db.

Steve Jones

steve@dkranch.net

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

Steve's right, should work in both though I don't have a SQL7 install to test on. It will fail if the login is already in the db as a user. Getting an error message you can post?

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Wei Cao SSC Veteran Points: 261 More actions · Answer 5

No. I don't get any error. The problem is that even after running

EXEC sp_addalias 'domain\userx', 'dbo'

All the db objects created by userx still owned by 'domain\userx' not by 'dbo'.

Wei Cao SSC Veteran Points: 261 More actions · Answer 6

I have tested the behavior I described in my last posting in a Win2k/SQL2k environment. And I know that If users prefix dbo. in any db object he/she created then the object IS owned by dbo. But we don't want to use prefixing all the objects with 'dbo.'