Alter authorization on view

• sunsilk10

  Old Hand

  Points: 336

  More actions

  December 15, 2020 at 9:27 pm

  #3820891

  Hi I have created a view in viewschema against table1 in tableschema

  I would like to change the owner of the view to to the tableschema.

  ALTER AUTHORIZATION ON  viewschema.viewName TO tableschema

  When I run the above, I get

  Cannot find the principal tableschema, because it does not exist or you do not have permission.

  Any idea what I am missing here?

• Eddie Wuerch

  SSChampion

  Points: 12538

  More actions

  December 16, 2020 at 7:01 pm

  #3821298

  Are you looking to transfer ownership (which does NOT change the schema), or to change the schema of the view from [viewschema] to [tableschema]?

  If you wish to change [viewschema].[viewName] to [tableschema].[viewName], then you use ALTER SCHEMA [<target_schema_name>] TRANSFER [<current_schema_name>].[<current_object_name>]:

  CREATE schema [tableschema];
  CREATE schema [viewschema];
  GO
  CREATE TABLE tableschema.table1(ID int);
  GO
  CREATE VIEW viewschema.viewName
  AS SELECT ID FROM tableschema.table1;
  GO
  SELECT * FROM viewschema.viewName;
  GO
  -- Change the schema of the target view
  ALTER SCHEMA [tableschema] 
    TRANSFER viewschema.viewName;
  GO
  SELECT * FROM tableschema.viewName;
  GO
  

  Eddie Wuerch
  MCM: SQL

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737535

  More actions

  Follow

  December 16, 2020 at 7:40 pm

  #3821329

  What Eddie said, but what are you trying to accomplish?