Multiple roles?

Hello. I was wondering if someone could help me understand better about
assigning a user to multiple roles. I created a role for some developers
from another group to be able to to DTS over some data (select, insert,
delete, update) into our local tables from their sql server. I also
assigned them to the db_ddladmin role. They tried to DTS our new data and
looked like they were able to delete the data in our local tables but their
bulk insert command to insert the data wasn't successful. I read and
thought that when assigning a user multiple roles the role with the most
priveleges takes in effect?
Thanks in advance.Yes, rights of several roles accumulate for a user, but db_ddladmin does not
grant BULK INSERT rights.
They need to be members of the bulkadmin server role in order to run BULK
INSERT in SS2000. In SS2005 you need ADMINISTER BULK OPERATIONS, plus
insert rights to the table and you may need ALTER TABLE permissions in some
cases.
See the Permissions subheading on the BULK INSERT command in either version
of Books Online.
RLF
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:uaHlkYSfHHA.444@.TK2MSFTNGP04.phx.gbl...
> Hello. I was wondering if someone could help me understand better about
> assigning a user to multiple roles. I created a role for some developers
> from another group to be able to to DTS over some data (select, insert,
> delete, update) into our local tables from their sql server. I also
> assigned them to the db_ddladmin role. They tried to DTS our new data and
> looked like they were able to delete the data in our local tables but
> their bulk insert command to insert the data wasn't successful. I read
> and thought that when assigning a user multiple roles the role with the
> most priveleges takes in effect?
> Thanks in advance.
>|||zz12 (IDontLikeSpam@.Nowhere.com) writes:
> Hello. I was wondering if someone could help me understand better about
> assigning a user to multiple roles. I created a role for some
> developers from another group to be able to to DTS over some data
> (select, insert, delete, update) into our local tables from their sql
> server. I also assigned them to the db_ddladmin role. They tried to
> DTS our new data and looked like they were able to delete the data in
> our local tables but their bulk insert command to insert the data wasn't
> successful. I read and thought that when assigning a user multiple
> roles the role with the most priveleges takes in effect?
I would guess that rather all privs are unioned together, and DENY
takes precendence over GRANT.
In any case, BULK INSERT requires membership in the bulkadmin server role,
which you don't seemed to have assign to your users.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Will look into it further. Thanks a bunch for both of your replies.
Totally appreciate it.
Take care.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99116B91E56CYazorman@.127.0.0.1...
> zz12 (IDontLikeSpam@.Nowhere.com) writes:
> I would guess that rather all privs are unioned together, and DENY
> takes precendence over GRANT.
> In any case, BULK INSERT requires membership in the bulkadmin server role,
> which you don't seemed to have assign to your users.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx