SP name

Question

SP name

VT-176769

SSChasing Mays

Points: 637
More actions
November 19, 2004 at 6:12 am

#87046

All,
When our developer creates a new SP. Server is not showing it dbo ownership on that object. System showing the object name as ServerName\LoginName.SPname. What’s the reason?
Regards
Vinu

Viewing 7 posts - 1 through 6 (of 6 total)

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

Taj Mr or Mrs. 500 Points: 596 More actions · Answer 1

dbo will show if the owner of the database creates a sp or any object. If you want to forcefully show the dbo then Create objects like this.

Create table dbo.tablename

create procedure dbo.procedurename

Tajammal Butt

VT-176769 SSChasing Mays Points: 637 More actions · Answer 2

Thanks for that.... unfortunately the problem is when the application tries to execute the sp, it will fail by saying object not found. i don't want to use dbo prefix every time while creating objects. I am looking for an alternative way, which will add objects under dbo regardless of login user. I have given db_owner role for each and every user.

Taj Mr or Mrs. 500 Points: 596 More actions · Answer 3

I had to struggle with the same issue about two years back. And the best solution I found was to add dbo prefix. I made this part of database documentation.

If your application looks for dbo then this should solve the problem..If you find any other way please let me know too.

Tajammal Butt

sa24 SSCrazy Eights Points: 8027 More actions · Answer 4

Make that user the owner of the database. The database owner shares the alias name dbo within a database and hence any objects created by that user would be prefixed dbo.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Do you want to run sp_changeobjectowner in a script once in a while or as a trigger on something? You may query sysobjects if there is an object of the type of Stored Procedure (xtype = 'P') and User ID (UID)does not belong to DBO. If one is found, change it by running sp_changeobjectowner for this object name

Yelena

Regards,Yelena Varsha

PBirch SSCrazy Eights Points: 8493 More actions · Answer 6

I posted a script here that did just that. I had to do it so that the scripts created by developers would run in the test environment.

Quand on parle du loup, on en voit la queue