In SQL Server 2000, users own object. In SQL Server 2005 and onward, users do not own objects, instead, they own schema. Thus, by default, the owner of an object should be the owner of its schema. But you can change it if necessary.
1. How to find the owner of an object
SELECT o.object_id,
o.type,
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'
ORDER BY o.name
GO
(http://www.sqlservercentral.com/Forums/Topic324773-149-1.aspx)
Or more concisely as below:
SELECT
so.[name] AS [Object Name]
, sch.[name] AS [Schema Name]
, USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [Owner]
, type_desc AS [ObjectType]
FROM sys.objects so
JOIN sys.schemas sch
ON so.[schema_id] = sch.[schema_id]
order by so.name
2. How to change the object owner?
SQL Server 2000 (use sp_changeobjectowner)
|
SQL Server 2005 and Up (use ALTER SCHEMA and ALTER AUTHORIZATION)
|
sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'
-- Changes the owner of the authors table to Corporate\GeorgeW.
EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'
|
ALTER SCHEMA (Transact-SQL)
Transfers a securable between schemas.
/*The following example modifies the schema HumanResources by transferring the table Address from schema Person into the schema.
*/
USE AdventureWorks2012;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO
ALTER AUTHORIZATION (Transact-SQL)
Changes the ownership of a securable.
/*A. Transfer ownership of a table
The following example transfers ownership of table Sprockets to user MichikoOsada. The table is located inside schema Parts.
*/
ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO
--The query could also look like the following:
ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;
GO
/*B. Transfer ownership of a view to the schema owner
The following example transfers ownership the view ProductionView06 to the owner of the schema that contains it. The view is located inside schema Production.
*/
ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER;
GO
/*C. Transfer ownership of a schema to a user
The following example transfers ownership of the schema SeattleProduction11 to user SandraAlayo.
*/
ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo;
GO
/*D. Transfer ownership of an endpoint to a SQL Server login
The following example transfers ownership of endpoint CantabSalesServer1 to JaePak. Because the endpoint is a server-level securable, the endpoint can only be transferred to a server-level principal.
*/
ALTER AUTHORIZATION ON ENDPOINT::CantabSalesServer1 TO JaePak;
GO
|
