SQL Server Performance Forum – Threads Archive
change owner
how can i change the owner of the object and stored procedure in sqlserver 2005. sp_changeobjectowner is not used in sql2005. i tried the ALTER AUTHRIZATION ALTER AUTHORIZATION ON Parts.Sprockets TO dbo;GO it excuted successfully, but it still showing the old object owner name instead of new owner name still the table name is Parts.Sprockets . thanks,
sp_changeobjectowner should still work. You just have to have CONTROL permission on the target object because if you don’t, you might get an error similar to the following: "Msg 15247, Level 16, State 1, Procedure sp_changeobjectowner, Line 17
User does not have permission to perform this action." SQL Server Helper
http://www.sql-server-helper.com
Try using ALTER SCHEMA… SQL Server 2005 Books Online
sp_changeobjectowner (Transact-SQL) Changes the owner of an object in the current database. Important:
This stored procedure only works with the objects available in Microsoft SQL Server 2000. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
In sql server 2005 the objects are owned by Schema and not by user as in SQL Server 2000. So here you need to alter/transfer object to another schema. EG. suppose you have a table TAB1 owner(schema) SCH1 and you want to transfer the ownership this object TAB1 to DBO. ALTER SCHEMA dbo TRANSFER SCH1.TAB1 this statement will transfer the ownership to DBO schema Madhu
BOL mentions on ALTER AUTHORISATION:
quote:
The SCHEMA OWNER option is only valid when you are transferring ownership of a schema-contained entity. SCHEMA OWNER will transfer ownership of the entity to the owner of the schema in which it resides. Only entities of class OBJECT, TYPE, or XML SCHEMA COLLECTION are schema-contained. If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.
If the principal is a Windows login without a corresponding SQL Server login, the principal must have CONTROL SERVER permission and TAKE OWNERSHIP permission on the database. If the principal is a SQL Server login, the principal cannot be mapped to a certificate or asymmetric key. Dependent aliases will be mapped to the new database owner. The DBO SID will be updated in both the current database and in sys.databases.
The SCHEMA OWNER option is only valid when you are transferring ownership of a schema-contained entity. SCHEMA OWNER will transfer ownership of the entity to the owner of the schema in which it resides. Only entities of class OBJECT, TYPE, or XML SCHEMA COLLECTION are schema-contained. If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.
If the principal is a Windows login without a corresponding SQL Server login, the principal must have CONTROL SERVER permission and TAKE OWNERSHIP permission on the database. If the principal is a SQL Server login, the principal cannot be mapped to a certificate or asymmetric key. Dependent aliases will be mapped to the new database owner. The DBO SID will be updated in both the current database and in sys.databases.
To execute Requires TAKE OWNERSHIP permission on the entity. If the new owner is not the user that is executing this statement, also requires either, 1) IMPERSONATE permission on the new owner if it is a user or login; or 2) if the new owner is a role, membership in the role, or ALTER permission on the role; or 3) if the new owner is an application role, ALTER permission on the application role. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>