I am using SQL Server 2005.

I have a login that is mapped to a user of the same name: MyUser. I also
have a schema MySchema.

I want to grant permissions on all objects in MySchema to MyUser, and at the
same time exclude all dbo objects. I would like to do this through some
role, if possible, and so not have to select each object in turn and tick
lots of boxes. I would also like MyUser to gain permissions on any new
objects in the MySchema schema, without having to go and explicitly grant
permissions every time a new object is added.

Is this a tall order? I haven't found a built-in role that I could use for
this, so I am wondering if this is possible.

TIA

Charles

> I want to grant permissions on all objects in MySchema to MyUser, and at
> the same time exclude all dbo objects. I would like to do this through
> some role, if possible, and so not have to select each object in turn and
> tick lots of boxes. I would also like MyUser to gain permissions on any
> new objects in the MySchema schema, without having to go and explicitly
> grant permissions every time a new object is added.

Try:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::MySchema TO MyRole;

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Charles Law" <blank@nowhere.com> wrote in message
news:ec0tlnS0IHA.4772@TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2005.
>
> I have a login that is mapped to a user of the same name: MyUser. I also
> have a schema MySchema.
>
> I want to grant permissions on all objects in MySchema to MyUser, and at
> the same time exclude all dbo objects. I would like to do this through
> some role, if possible, and so not have to select each object in turn and
> tick lots of boxes. I would also like MyUser to gain permissions on any
> new objects in the MySchema schema, without having to go and explicitly
> grant permissions every time a new object is added.
>
> Is this a tall order? I haven't found a built-in role that I could use for
> this, so I am wondering if this is possible.
>
> TIA
>
> Charles
>
>

Hi Dan

Thanks for the quick reply. That looks like just the ticket. It wasn't
obvious to me how I would do that using the GUI in SSMS, but after seeing
your suggestion I think I have found it.

Cheers.

Charles


"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:918280AA-5C87-44CE-80E9-3AD0CA3CE9E4@microsoft.com...
>> I want to grant permissions on all objects in MySchema to MyUser, and at
>> the same time exclude all dbo objects. I would like to do this through
>> some role, if possible, and so not have to select each object in turn and
>> tick lots of boxes. I would also like MyUser to gain permissions on any
>> new objects in the MySchema schema, without having to go and explicitly
>> grant permissions every time a new object is added.
>
> Try:
>
> GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::MySchema TO
> MyRole;
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Charles Law" <blank@nowhere.com> wrote in message
> news:ec0tlnS0IHA.4772@TK2MSFTNGP03.phx.gbl...
>>I am using SQL Server 2005.
>>
>> I have a login that is mapped to a user of the same name: MyUser. I also
>> have a schema MySchema.
>>
>> I want to grant permissions on all objects in MySchema to MyUser, and at
>> the same time exclude all dbo objects. I would like to do this through
>> some role, if possible, and so not have to select each object in turn and
>> tick lots of boxes. I would also like MyUser to gain permissions on any
>> new objects in the MySchema schema, without having to go and explicitly
>> grant permissions every time a new object is added.
>>
>> Is this a tall order? I haven't found a built-in role that I could use
>> for this, so I am wondering if this is possible.
>>
>> TIA
>>
>> Charles
>>
>>
>