How does one change the Schema Owner in a Access Governance Suite MS SQL DB?

  • 7011107
  • 03-May-2011
  • 19-Oct-2012

Resolution

How does one change the schema owner, etc., in the one DB to that of another DB ID (i.e. in an upgrade moving the current Prod DB to now be the Dev DB) in MS SQL?

According to the MSDN article at http://msdn.microsoft.com/en-us/library/ms173423.aspx, you would do the following:

ALTER SCHEMA schema_name 
   TRANSFER [ <entity_type> :: ] securable_name [;]<entity_type> ::=
    {
    Object | Type | XML Schema Collection
    }<dl><dt> schema_name</dt><dd>
Is the name of a schema in the current database, into which the securable will be moved. Cannot be SYS or INFORMATION_SCHEMA.</dd><dt><entity_type></dt><dd>
Is the class of the entity for which the owner is being changed. Object is the default.</dd><dt> securable_name</dt><dd>
Is the one-part or two-part name of a schema-contained securable to be moved into the schema</dd></dl>