SQL Server Performance

How to Change the owned Schemas for a user

Discussion in 'SQL Server 2005 General DBA Questions' started by y.koteswarrao, Sep 25, 2010.

  1. y.koteswarrao New Member

    Hi,
    I have given a user db_datareader owned_schema and now i what to change it to db_datawriter ,but unable to change because db_datareader is in disabled status , ihave given through SSMS.
    Can any one please let me known the procedure to change the owned schema.
    Thanks
    Koteswar Rao
    y.koteswarrao, Sep 25, 2010
    #2

  2. Luis Martin Moderator

    Check:
    http://www.techtalkz.com/microsoft-sql-server/150729-change-schemas-owned.html
    Luis Martin, Sep 26, 2010
    #3

  3. satya Moderator

    You can also use the TSQL as per BOL
    USE AdventureWorks;
    GO
    CREATE TYPE Production.TestType FROM [varchar](10) NOT NULL ;
    GO
    -- Check the type owner
    SELECT sys.types.name, sys.types.schema_id, sys.schemas.name
    FROM sys.types JOIN sys.schemas
    ON sys.types.schema_id = sys.schemas.schema_id
    WHERE sys.types.name = 'TestType' ;
    GO
    -- Change the type to the Person schema
    ALTER SCHEMA Person TRANSFER type::production.TestType ;
    GO
    -- Check the type owner
    SELECT sys.types.name, sys.types.schema_id, sys.schemas.name
    FROM sys.types JOIN sys.schemas
    ON sys.types.schema_id = sys.schemas.schema_id
    WHERE sys.types.name = 'TestType' ;
    GO
    satya, Sep 30, 2010
    #4

Share This Page