Post reply

Alter authorization on view

  • 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?

  • 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

  • December 16, 2020 at 7:40 pm

    #3821329

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply