Post reply

Schema based objects

  • October 22, 2007 at 2:25 pm

    #183094

    Hello,

    I would like to know whether there is any work around in finding out different objects (tables, stored procedures, endpoints etc) that are being owned by a schema. On my development box there are only 9 databases including system default and I have checked in SSMS -> Database -> Security -> Schemas but could not find any. I'm not able to drop the user login from the box.

    Would appreciate if anyone has a script to find out the schema related objects.

    TIA


    Lucky

  • October 30, 2007 at 8:14 am

    #746654

    Hello Peers,

    I would appreciate any solution to the issue I'm facing.

    TIA


    Lucky

  • December 2, 2007 at 1:16 pm

    #756305

    Users are not schemas. Do you mean you need to know which object is owned by a user?

    If this is the user schema, then you could easily see from object listing, which objects are in this schema.

    This might help: http://blogs.interfacett.com/jeff-jones-blog/2006/9/27/sql-server-2005-displaying-object-owners.html

  • December 2, 2007 at 4:59 pm

    #756340

    Appreciate you bumping your own post, but the reason why not many folks have responded is we just don't know what you're talking about....

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 15, 2008 at 4:13 pm

    #768390

    In SQL 2000 the owner or schema of an object was indicated by the uid value in sysobjects and could be looked up in sysusers.

    In SQL 2005 the owner of an object may be indicated by principal_id (links to sys.database_principals), but if this is null then ownership defaults to the schema_id (links to sys.schemas).

    -- SQL 2000

    SELECT o.xtype, QUOTENAME(u.name) + '.' + QUOTENAME(o.name) AS Object, u.name AS Owner

    FROM sysobjects AS o

    INNER JOIN sysusers AS u ON u.uid = o.uid

    WHERE ( u.name = 'dbo' )

    -- SQL 2005

    SELECT o.type_desc, QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS Object,

    COALESCE(p.name, s.name) AS OwnerName

    FROM sys.all_objects AS o

    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id

    LEFT JOIN sys.database_principals AS p ON p.principal_id = o.principal_id

    WHERE COALESCE(p.name, s.name) = 'dbo'

  • January 16, 2008 at 7:05 am

    #768628

    Scott,

    Thanks for sharing the code. Nice Report!

    Happy T-SQLing

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • February 5, 2008 at 8:22 am

    #775147

    This seems related so I am posting here. I am having a similar issue. It seems that Microsoft has added all this nice support for Schemas EXCEPT where it counts!? For example, my Procs are now owned by other Schemas and not "dbo". However, in the CATCH block I cannot determine which Proc is actually getting the error!?

    For example:

    1.) Create two Schemas: Contact & Member.

    2.) Create two Proc: [Contact].[Save] & [Member].[Save] adding the appropriate TRY CATCH blocks.

    3.) Raise an Error intentionally in the Procs and try to use the ERROR_PROCEDURE() call.

    Guess what, they both come back as "Save" without the Schema! I've been seeing this a lot with other intrinsic SQL Server functions. It's now pretty much very difficult or impossible to determine object owners.

    For instance, this is the only way to determine a full object name easily:

    Schema_Name(Convert(Int, ObjectPropertyEx(Object_ID(@ObjectName), 'SchemaId'))) + '.' + Object_Name(Object_ID(@ObjectName)))

    So basically, I am asking, is it possible to get the Schema returned from the new ERROR funcrtions? If not I am going back to everything being "dbo"!

  • April 2, 2008 at 8:31 am

    #797277

    To tymberwyld -

    Did you find a resolution for the Schema issue when Raising Errors etc?

    I'm curious to know because right now I log errors to a table, that tells me what the proc was and what line the error occured on (no schema). We've been considering schemas for a rewrite of a major database, but if we can't tell where the errors come from I might want to reconsider.

  • April 2, 2008 at 8:51 am

    #797302

    I'm not able to test this at the moment, but it's worth a try:

    OBJECT_SCHEMA_NAME(@@PROCID) + '.' + ERROR_PROCEDURE()

  • April 2, 2008 at 9:18 am

    #797325

    Works great Scot, Thank you!

    Here's an example for testing what Scot gave us.

    CREATE PROCEDURE prctest

    AS

    BEGIN

    DECLARE @t AS TABLE(col1 int NOT NULL, col2 int NOT NULL)

    BEGIN TRY

    -- Throws an Error since col2 can't be null

    INSERT @t(col1)

    VALUES (1)

    END TRY

    BEGIN CATCH

    SELECT OBJECT_SCHEMA_NAME(@@ProcID) + '.' + COALESCE(ERROR_PROCEDURE(),'')

    END CATCH

    END

    GO

    EXECUTE prcTest

    GO

    DROP PROCEDURE prcTest

    Result

    dbo.prctest

    One thing to note, if you call a stored procedure to do your error logging, it's going to be getting the error logging stored procedure's ProcID. Therefore, you'll need to pass in the ProcID from the erroring procedure to your logging stored procedure.

  • April 4, 2008 at 3:32 am

    #798281

    Thanks Scott! I was just passing in the name of my Procs manually, this will help alleviate that!

Viewing 11 posts - 1 through 10 (of 10 total)

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