Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36

    Question Unanswered: Owner of Stored Procedure

    I am trying to get the owner of the stored procedures. This query is not giving the owner:

    SELECT u.Name as UserName
    , o.Name as SprocName
    FROM sysobjects o
    INNER JOIN sysusers u on o.uid = u.uid
    WHERE type = 'P' order by o.name

    Thanks in advance for your help.
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    version of SQL server please?

    By owner do you mean schema (e.g. dbo.employees)?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36

    The Schema

    Yes. The schema. I need to know if dbo is the schema for it or not.

    Thanks.
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It only became known as schema in 2005. It was owner in 2000. Please let us know your SQL Server version - there are different ways of skinning the cat depending on the version.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36

    SQL Server 2000 and Higher

    The query needs to work on SQL Server 2000, 2005, and 2008. Is it possible or do I need to detect the SQL Server version and run different queries?
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    From 2000 to 2005 the prefix was brought in line from the SQL Server concept of owner to the ANSI (now ISO) SQL concept of schema. There are backwards compatibility views that should work ok for you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36
    pootle flump, What query should I use?
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your query works for me in 2005 (not upgraded databases). There is some text in BoL, possibly relevant:
    uid
    smallint
    Schema ID of the owner of the object.
    For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner.
    http://www.dbforums.com/local/note.gifImportant: If you use any of the following SQL Server 2005 DDL statements, you must use the sys.objects catalog view instead of sys.sysobjects. CREATE | ALTER | DROP USER CREATE | ALTER | DROP ROLE CREATE | ALTER | DROP APPLICATION ROLE CREATE SCHEMA ALTER AUTHORIZATION ON OBJECT
    Overflows or returns NULL if the number of users and roles exceeds 32,767.
    For more information, see Querying the SQL Server System Catalog.
    Where are you running your code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gfinzer
    This query is not giving the owner
    Also - please could you tell us what it is giving?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36
    Okay. Someone gave me this and it shows the owner correctly. However, I don't know if this works under SQL Server 2000:

    exec sp_stored_procedures
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The FinzMan, visiting the forums!
    You couldn't come to me for help first, dude?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are trying to determine the schema within the sproc, try this:
    Code:
    select	Schemas.name
    from	sys.procedures Procedures
    		inner join sys.schemas Schemas on Procedures.schema_id = Schemas.schema_id
    where	object_id = @@PROCID
    If you are trying to determine the owner outside the sproc, you are going to run into a Catch 22. You'd have to know the schema of the procedure before you could look up what schema it is in. Procedure name is not sufficient, as there could be two sprocs with the same name, but in different schemas.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36
    Does it work under SQL Server 2000 and up?

    Revised:
    select Schemas.name as SchemaName, Procedures.name as SprocName
    from sys.procedures Procedures
    inner join sys.schemas Schemas on Procedures.schema_id = Schemas.schema_id
    Greg Finzer
    http://www.kellermansoftware.com
    .NET Components

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •