November 21, 2006 at 3:09 pm
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.
November 21, 2006 at 11:15 pm
In SQL2005:
SELECT SCHEMA_NAME(OBJECTPROPERTYEX(OBJECT_ID('yourTablesName'),N'SchemaId'))
Julian Kuiters
juliankuiters.id.au
November 22, 2006 at 7:13 am
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
April 9, 2015 at 1:53 pm
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