-
problem with owners. Urgent help needed
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?
-
use sp_changeobjectowner details in BOL
-
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?
-
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'
-
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.
-
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.
-
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?
-
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.
-
You can use sp_change_users_login to remap sql login to db user after restoring db.
-
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.
Last edited by Andrei; 04-28-2006 at 03:47 PM.
-
Tried remap dbo77 with sp_change_users_login?
-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|