CREATE PROCEDURE Sp_changeobjectowner @objname NVARCHAR(517),
-- may be "[owner].[object]"
@newowner SYSNAME
-- must be entry from sysusers
AS
SET nocount ON
SET ansi_padding ON
DECLARE @objid INT,
@newuid SMALLINT
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF ( Is_member('db_owner') = 0 )
AND ( Is_member('db_securityadmin') = 0
OR Is_member('db_ddladmin') = 0 )
BEGIN
RAISERROR(15247,-1,-1)
RETURN ( 1 )
END
-- RESOLVE OBJECT NAME (CANNOT BE A CHILD OBJECT: TRIGGER/CONSTRAINT) --
SELECT @objid = Object_id(@objname, 'local')
IF ( @objid IS NULL )
OR (SELECT parent_obj
FROM sysobjects
WHERE id = @objid) <> 0
OR Objectproperty(@objid, 'IsMSShipped') = 1
OR Objectproperty(@objid, 'IsSystemTable') = 1
OR Objectproperty(@objid, 'ownerid') IN ( 0, 3, 4 )
OR --public, INFORMATION_SCHEMA, system_function_schema
-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
EXISTS (SELECT *
FROM sysdepends d
WHERE d.depid = @objid -- A dependency on this object
AND d.deptype > 0 -- that is enforced
AND @objid <> d.id
-- that isn't a self-reference (self-references don't use object name)
AND @objid <>
-- And isn't a reference from a child object (also don't use object name)
(SELECT o.parent_obj
FROM sysobjects o
WHERE o.id = d.id))
BEGIN
-- OBJECT NOT FOUND
RAISERROR(15001,-1,-1,@objname)
RETURN 1
END
-- RESOLVE NEW OWNER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) --
-- Disallow aliases, and public cannot own objects --
SELECT @newuid = uid
FROM sysusers
WHERE NAME = @newowner
AND isaliased = 0
AND uid NOT IN ( 0, 3, 4 )
--public, INFORMATION_SCHEMA, system_function_schema
IF @newuid IS NULL
BEGIN
EXECUTE Sp_msadduser_implicit_ntlogin
@newowner
SELECT @newuid = uid
FROM sysusers
WHERE NAME = @newowner
AND isaliased = 0
AND NAME <> 'public'
END
IF @newuid IS NULL
BEGIN
RAISERROR(15410,-1,-1,@newowner)
RETURN ( 1 )
END
-- CHECK IF CHANGING OWNER OF OBJECT OR ITS CHILDREN WOULD PRODUCE A DUPLICATE
IF EXISTS (SELECT *
FROM sysobjects
WHERE uid = @newuid
AND NAME IN (SELECT NAME
FROM sysobjects
WHERE id = @objid
OR parent_obj = @objid))
BEGIN
RAISERROR(15505,-1,-1,@objname,@newowner)
RETURN ( 1 )
END
-- DO THE OWNER TRANSFER (WITH A WARNING) --
RAISERROR(15477,-1,-1)
BEGIN TRANSACTION
-- Locks Object and increments schema_ver.
DBCC lockobjectschema(@objname)
-- drop permissions (they'll be incorrect with new owner) --
DELETE syspermissions
WHERE id = @objid
UPDATE sysobjects
SET uid = @newuid
WHERE id = @objid
UPDATE sysobjects
SET uid = @newuid
WHERE parent_obj = @objid
COMMIT TRANSACTION
RETURN 0 -- sp_changeobjectowner
go
