Home Forums SQL Server 2014 Administration - SQL Server 2014 Consequences of making "dbo", directly, the owner of db_datareader schema RE: Consequences of making "dbo", directly, the owner of db_datareader schema

  • February 23, 2018 at 3:04 pm

    #1981013

    Chris Harshman - Friday, February 23, 2018 2:37 PM

    goher2000 - Friday, February 23, 2018 1:02 PM

    For the fixed database roles such as db_datareader, by default the role and a schema of the same name are created. The owner of the role is "dbo" and that role owns the same-named schema. What would happen if I made "dbo", directly, the owner of the db_datareader schema and deleted the db_datareader role?

    What are you trying to achieve?  Typically you wouldn't be changing system created objects like this, and that schema won't have any objects in it anyway.

    You can create objects in those schemas and work with them like other objects - you can't see them but they are there:

    CREATE TABLE [db_datareader].Test
    (SomeID int);

    SELECT [name], SCHEMA_NAME(schema_id) as SchemaName
    FROM sys.objects
    WHERE [name] = 'Test';

    SELECT [name], SCHEMA_NAME([schema_id])as SchemaName
    FROM sys.tables
    WHERE [name] = 'Test';

    INSERT [db_datareader].Test
    values
    (2);

    SELECT *
    FROM [db_datareader].Test;

    Sue