SQL Server 2005

I understand database roles. I *thoght* I understood Schemas in 2005. I
just noticed that there is a db_datareader schema and a db_datareader
database role in one of my user databases. The schema of db_datareader
makes no sense to me. As stated in Microsoft articles, "A database schema
is a distinct namespace that is separate from a database user. You can think
of a schema as a container of objects."

The quote makes sense. Having a namespace/container for db_datareader BY
DEFAULT does not sit well in my head.

Could someone clear this up for me? I imagine this topic has been discussed
before - sorry I didn't find it.

Mark

Mark,

I agree that the schema of db_datareader makes no real sense. It seems that
a part of trying to get the new schema model in place, SQL Server just
automatically creates schemas for every standard role (except public) and
user (including guest). If you upgraded a SQL Server 2000 database to 2005,
you will also see schemas created for every eligible user in that database.

However, CREATE USER does not also create a schema for that user. So a user
will be just fine without a similarly named schema. If you want to have a
user's default database be dbo, you can use the WITH DEFAULT_SCHEMA = dbo to
make it explicit.

So, you should be able to get rid of some of those without problems.
(Provided nothing is using the schema and I don't believe that you can get
rid of 'guest'.) E.g.

DROP SCHEMA [db_datareader]

Having said that, they are clutter but probably do not hurt anything.

RLF

"Mark" <mark@idonotlikespam.com> wrote in message
news:%23PDYhe8zIHA.4004@TK2MSFTNGP03.phx.gbl...
> SQL Server 2005
>
> I understand database roles. I *thoght* I understood Schemas in 2005. I
> just noticed that there is a db_datareader schema and a db_datareader
> database role in one of my user databases. The schema of db_datareader
> makes no sense to me. As stated in Microsoft articles, "A database schema
> is a distinct namespace that is separate from a database user. You can
> think of a schema as a container of objects."
>
> The quote makes sense. Having a namespace/container for db_datareader BY
> DEFAULT does not sit well in my head.
>
> Could someone clear this up for me? I imagine this topic has been
> discussed before - sorry I didn't find it.
>
> Mark
>

Do you know if is supported to drop them? It seems to work fine.

Ola Hallengren
http://ola.hallengren.com



"Russell Fields" wrote:

> Mark,
>
> I agree that the schema of db_datareader makes no real sense. It seems that
> a part of trying to get the new schema model in place, SQL Server just
> automatically creates schemas for every standard role (except public) and
> user (including guest). If you upgraded a SQL Server 2000 database to 2005,
> you will also see schemas created for every eligible user in that database.
>
> However, CREATE USER does not also create a schema for that user. So a user
> will be just fine without a similarly named schema. If you want to have a
> user's default database be dbo, you can use the WITH DEFAULT_SCHEMA = dbo to
> make it explicit.
>
> So, you should be able to get rid of some of those without problems.
> (Provided nothing is using the schema and I don't believe that you can get
> rid of 'guest'.) E.g.
>
> DROP SCHEMA [db_datareader]
>
> Having said that, they are clutter but probably do not hurt anything.
>
> RLF
>
> "Mark" <mark@idonotlikespam.com> wrote in message
> news:%23PDYhe8zIHA.4004@TK2MSFTNGP03.phx.gbl...
> > SQL Server 2005
> >
> > I understand database roles. I *thoght* I understood Schemas in 2005. I
> > just noticed that there is a db_datareader schema and a db_datareader
> > database role in one of my user databases. The schema of db_datareader
> > makes no sense to me. As stated in Microsoft articles, "A database schema
> > is a distinct namespace that is separate from a database user. You can
> > think of a schema as a container of objects."
> >
> > The quote makes sense. Having a namespace/container for db_datareader BY
> > DEFAULT does not sit well in my head.
> >
> > Could someone clear this up for me? I imagine this topic has been
> > discussed before - sorry I didn't find it.
> >
> > Mark
> >
>
>
>

Ola,

If you don't need them for anything (and I suggest that you never use them)
then go ahead and drop them. Yes, it is supported to drop them.

RLF

"Ola Hallengren" <OlaHallengren@discussions.microsoft.com> wrote in message
news:B28396EE-3FC4-4545-9EA7-552B7CA9DE63@microsoft.com...
> Do you know if is supported to drop them? It seems to work fine.
>
> Ola Hallengren
> http://ola.hallengren.com
>
>
>
> "Russell Fields" wrote:
>
>> Mark,
>>
>> I agree that the schema of db_datareader makes no real sense. It seems
>> that
>> a part of trying to get the new schema model in place, SQL Server just
>> automatically creates schemas for every standard role (except public) and
>> user (including guest). If you upgraded a SQL Server 2000 database to
>> 2005,
>> you will also see schemas created for every eligible user in that
>> database.
>>
>> However, CREATE USER does not also create a schema for that user. So a
>> user
>> will be just fine without a similarly named schema. If you want to have
>> a
>> user's default database be dbo, you can use the WITH DEFAULT_SCHEMA = dbo
>> to
>> make it explicit.
>>
>> So, you should be able to get rid of some of those without problems.
>> (Provided nothing is using the schema and I don't believe that you can
>> get
>> rid of 'guest'.) E.g.
>>
>> DROP SCHEMA [db_datareader]
>>
>> Having said that, they are clutter but probably do not hurt anything.
>>
>> RLF
>>
>> "Mark" <mark@idonotlikespam.com> wrote in message
>> news:%23PDYhe8zIHA.4004@TK2MSFTNGP03.phx.gbl...
>> > SQL Server 2005
>> >
>> > I understand database roles. I *thoght* I understood Schemas in 2005.
>> > I
>> > just noticed that there is a db_datareader schema and a db_datareader
>> > database role in one of my user databases. The schema of db_datareader
>> > makes no sense to me. As stated in Microsoft articles, "A database
>> > schema
>> > is a distinct namespace that is separate from a database user. You can
>> > think of a schema as a container of objects."
>> >
>> > The quote makes sense. Having a namespace/container for db_datareader
>> > BY
>> > DEFAULT does not sit well in my head.
>> >
>> > Could someone clear this up for me? I imagine this topic has been
>> > discussed before - sorry I didn't find it.
>> >
>> > Mark
>> >
>>
>>
>>

Thanks, Russel. I will do that.

Ola Hallengren
http://ola.hallengren.com



"Russell Fields" wrote:

> Ola,
>
> If you don't need them for anything (and I suggest that you never use them)
> then go ahead and drop them. Yes, it is supported to drop them.
>
> RLF
>
> "Ola Hallengren" <OlaHallengren@discussions.microsoft.com> wrote in message
> news:B28396EE-3FC4-4545-9EA7-552B7CA9DE63@microsoft.com...
> > Do you know if is supported to drop them? It seems to work fine.
> >
> > Ola Hallengren
> > http://ola.hallengren.com
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Mark,
> >>
> >> I agree that the schema of db_datareader makes no real sense. It seems
> >> that
> >> a part of trying to get the new schema model in place, SQL Server just
> >> automatically creates schemas for every standard role (except public) and
> >> user (including guest). If you upgraded a SQL Server 2000 database to
> >> 2005,
> >> you will also see schemas created for every eligible user in that
> >> database.
> >>
> >> However, CREATE USER does not also create a schema for that user. So a
> >> user
> >> will be just fine without a similarly named schema. If you want to have
> >> a
> >> user's default database be dbo, you can use the WITH DEFAULT_SCHEMA = dbo
> >> to
> >> make it explicit.
> >>
> >> So, you should be able to get rid of some of those without problems.
> >> (Provided nothing is using the schema and I don't believe that you can
> >> get
> >> rid of 'guest'.) E.g.
> >>
> >> DROP SCHEMA [db_datareader]
> >>
> >> Having said that, they are clutter but probably do not hurt anything.
> >>
> >> RLF
> >>
> >> "Mark" <mark@idonotlikespam.com> wrote in message
> >> news:%23PDYhe8zIHA.4004@TK2MSFTNGP03.phx.gbl...
> >> > SQL Server 2005
> >> >
> >> > I understand database roles. I *thoght* I understood Schemas in 2005.
> >> > I
> >> > just noticed that there is a db_datareader schema and a db_datareader
> >> > database role in one of my user databases. The schema of db_datareader
> >> > makes no sense to me. As stated in Microsoft articles, "A database
> >> > schema
> >> > is a distinct namespace that is separate from a database user. You can
> >> > think of a schema as a container of objects."
> >> >
> >> > The quote makes sense. Having a namespace/container for db_datareader
> >> > BY
> >> > DEFAULT does not sit well in my head.
> >> >
> >> > Could someone clear this up for me? I imagine this topic has been
> >> > discussed before - sorry I didn't find it.
> >> >
> >> > Mark
> >> >
> >>
> >>
> >>
>
>
>