How to find the owner of the table in MSSQL 2005?

  • Hi,

    The question is: the default owner of the table was set to the principal name by the "ALTER AUTHORIZATION" query.

    Now, how can I see the name of the owner, and can I get it using SQL query?

    Thanks.

  • In SQL2005:

    SELECT SCHEMA_NAME(OBJECTPROPERTYEX(OBJECT_ID('yourTablesName'),N'SchemaId'))


    Julian Kuiters
    juliankuiters.id.au

  • In 2005, an object's owner is schema owner by default. It can be changed to other principals by "ALTER AUTHORIZATION", in this case, the principal_id will not be null i the sys.objects table. To get table (view, SP,udf, etc) owner:

    SELECT

    o.object_id, o.name,

    CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)

    ELSE (SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.schema_id=o.schema_id and s.principal_id=dp.principal_id)

    END as Owner

    FROM

    sys.objects o

    WHERE

    type='U'

    GO

    E.g.:

    USE AdventureWorks

    GO

    select

    * FROM sys.objects where type='U'

    select

    * from sys.schemas

    SELECT

    * from sys.database_principals

    GO

     

    SELECT o.object_id, o.name,

    CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)

    ELSE (SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.schema_id=o.schema_id and s.principal_id=dp.principal_id)

    END as Owner

    FROM

    sys.objects o

    WHERE

    type='U'

    GO

    Change one table owner to guest:

    ALTER

    AUTHORIZATION ON Production.ProductSubcategory TO guest

    GO

    SELECT o.object_id, o.name,

    CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)

    ELSE (SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.schema_id=o.schema_id and s.principal_id=dp.principal_id)

    END as Owner

    FROM

    sys.objects o

    WHERE

    type='U'

    GO

    Restore the table owner to schema owner:

    ALTER

    AUTHORIZATION ON Production.ProductSubcategory TO schema owner

    GO

  • Can it be seen in SSMS anywhere once the ALTER AUTHORIZATION has been done? It doesn't look like it changes anything there.

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

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