Post reply

Stored Procedure [OWNER]

  • April 1, 2004 at 8:52 am

    #82354

    I've read several articles recently, stating that it is good practice to name an Owner for your stored procedure. When I say owner I mean the owner described in the following syntax: CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS.

    From what I understand this saves on overheads as it sops SQL Server looking for an owner before executing the sp.

    My question is this: My database is the back-end to an asp/asp.net website. At present I have no owners assigned to my sp's(!). All connections from the website go through one connection string using a login called 'webuser'. The 'webuser' login is set up as user in SQL Server. All access to the database is done through this connection.

    Is this the 'owner' I need to set in my sp's?

    Thanks for any assistance you might be able to offer.

    Windows 2008 Server | SQL Server 2008

  • April 1, 2004 at 5:30 pm

    #501541

    In a production environment all objects are likely (and probably should be) owned by dbo.  If you don't say SQL Server checks for objects owned by the logged in user and if not found then checks for objects owned by 'dbo'.  There was a great article recently about this if your interested see http://qa.sqlservercentral.com/columnists/awarren/worstpracticesobjectsnotownedbydbo.asp

    It should be evident who is the owner by looking at the objects via EM.  If your tables, views procs are not owned by dbo I would suggest changing this and altering the procs etc to reflect this.  Your 'webuser' login needs to be given appropriate authority.

    Francis

  • April 1, 2004 at 11:52 pm

    #501550

    In fact, it should be the calling part that should qualify the owner of the called object.

    Because sqlserver 6.5,7.0,2K did not implement schema (sql2k5 will)

    and has a garbage-owner called "dbo", keeping in mind the problems

    with ownership-chaining(security/securitychecking), it's considered best practise to use dbo only.

    This will avoid the chaining problems and make it more supportable for the dba's who are confronted with apps-execution problems.

    checkout qa.sqlservercentral.com/columnists/sjones/istheschemastillneeded.asp and the comments, as well as the article and comments fhanlon mentioned

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • April 2, 2004 at 12:46 am

    #501553

    Thanks a lot! Things are much clearer.

    Windows 2008 Server | SQL Server 2008

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

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