problem with owners. Urgent help needed

[Andrei]

Hi everybody!
I create my database by restore from server’s backup. Every object there is owned by dbo77 user. I want to execute stored procedure in following way: exec sp1 and not – exec dbo77.sp1, when I connect with the same user. I create new Login – dbo77 in Securities folder of my server (LOCAL). And when I connect by this user to that database, I still can’t use syntax: exec sp1.
An error says that my login dbo77 does not have an associated user name. When I try to mark that database in dbo77 ->property->Database_Access it writes an error that dbo77 already exists.
How can I make this db work with syntax exec sp1, if I login with same login name as the owner of sp1?

[tom karney]

use sp_changeobjectowner details in BOL

[Andrei]

you mean to make dbo owner of all objects in DB.
And there is no chance not to put owner before stored procedure, if owner iz not dbo?

[tom karney]

yes If you have a lot of objects can use commands of style below to generate actual command example for procs

select 'sp_changedbobjectowner ',''''+name+'''',',','''dbo''' from sysobjects where type = 'p'

[Andrei]

But in this DB I have a references to user functions using dbo77 as prefix, making all objects owner dbo will stop them working. I read in a MS article, that if you login by some user, you can call objects just by name, with no need using prefix. I want just make this work!
I create login in my db server which has same name dbo77. But I think that that login dbo77 is not asociated with my db owner and it doesn't recognise it during login.

[tom karney]

If you actualy have a user or group called dbo77 then using grants for them grant execute on procname to dbo77 then they would be able to execute it.

[Andrei]

I think I found out what is going on. My login name and database owners are the same, but theirs SID are different!!! How can I change them to match? Or how Login can be created with defined SID?

[tom karney]

You could try changing DB owner to SA and then removing the ID as a DBuser and then from SQL . May then get message can't drop it because it owns objects in DB in which case you are back to changing their owner.

[rmiao]

You can use sp_change_users_login to remap sql login to db user after restoring db.

[Andrei]

now I asociate db user with my login, but I still cannot use syntax where I can omit owner in prefix. I know exactly that it must work. I cannot find that microsoft article, but here is one reference:
first message, (a)
http://www.thescripts.com/forum/thread441379.html
I also have istalled VS2005 in my PC, may be it update some functinons or settings that changes default behavior.

hm... I also check now connected user by
SELECT user_name()
and the ouput was DBO
but in the sql profiler the executing user is still dbo77
Strange isn't it?



Sorry for posible low gramar in my articles.

[rmiao]

Tried remap dbo77 with sp_change_users_login?

[Andrei]

I finally find out the problem source.
I turn off db_owner role for my user, and 'SELECT user_name()' returns me dbo77. Everything works fine now!