Granting create table to database role

Hi All,
You'll have to forgive me for not knowing too much about SQL Server
security. Here is the scenario:
I have a user called FDS, which owns the database and all user tables. I
have two database roles, one FDS_User which has select/update permissions on
all tables, and one FDS_Admin. I want any users with the FDS_Admin role to
be able to modify the structure of tables (actually only three of them but
I'll settle for all if it's easier). The user 'ryan' has both roles, and
can select from any tables, but can't create/modify them. The create
statement returns "Specified owner name 'fds' either does not exist or you
do not have permission...", and the alter table statement returns "User does
not have permission to perform this operation on table 'Ryan'."
In enterprise manager -> database properties -> permissions, the FDS_Admin
role has a tick in the Create Table box.
Does anyone have any ideas how I can get the above to work? Let me know if
you need any more information.
Thanks in advance
RyanHi
Add these users to db_owner database fixed role.
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eX1JIoU0EHA.752@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> You'll have to forgive me for not knowing too much about SQL Server
> security. Here is the scenario:
> I have a user called FDS, which owns the database and all user tables. I
> have two database roles, one FDS_User which has select/update permissions
on
> all tables, and one FDS_Admin. I want any users with the FDS_Admin role
to
> be able to modify the structure of tables (actually only three of them but
> I'll settle for all if it's easier). The user 'ryan' has both roles, and
> can select from any tables, but can't create/modify them. The create
> statement returns "Specified owner name 'fds' either does not exist or you
> do not have permission...", and the alter table statement returns "User
does
> not have permission to perform this operation on table 'Ryan'."
> In enterprise manager -> database properties -> permissions, the FDS_Admin
> role has a tick in the Create Table box.
> Does anyone have any ideas how I can get the above to work? Let me know
if
> you need any more information.
> Thanks in advance
> Ryan
>|||Thanks,
That has worked, but I've noticed that I can remove the FDS_Admin role, and
the user can still create/alter tables. Is this the only way to do this?
I'll have a look into what else db_owner can do but it seems a bit risky to
add every user to the db_owner role. Any thoughts?
Thanks again
Ryan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eIt49AW0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> Hi
> Add these users to db_owner database fixed role.
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:eX1JIoU0EHA.752@.TK2MSFTNGP12.phx.gbl...
> on
> to
> does
> if
>|||Hi
In our company we gave permissions only for EXECUTION on stored procedures
,not on underlaying tables.
If you want the users to be able SELECT/UPDATE/DELETE/INSERT don't grant
them permissions on actual tables ,instead create stored procedures that
will manipulate against tables.
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:uIfKVIW0EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Thanks,
> That has worked, but I've noticed that I can remove the FDS_Admin role,
and
> the user can still create/alter tables. Is this the only way to do this?
> I'll have a look into what else db_owner can do but it seems a bit risky
to
> add every user to the db_owner role. Any thoughts?
> Thanks again
> Ryan
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eIt49AW0EHA.3452@.TK2MSFTNGP14.phx.gbl...
I[vbcol=seagreen]
permissions[vbcol=seagreen]
role[vbcol=seagreen]
and[vbcol=seagreen]
know[vbcol=seagreen]
>|||Unfortunately the application has been designed and written. It might be
some work to change it all now! Surely there is an easy way to create a
role or otherwise which will allow users to add or modify tables, but not
give them full db_owner access?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ud5F0YW0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> Hi
> In our company we gave permissions only for EXECUTION on stored procedures
> ,not on underlaying tables.
> If you want the users to be able SELECT/UPDATE/DELETE/INSERT don't grant
> them permissions on actual tables ,instead create stored procedures that
> will manipulate against tables.
>
>
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:uIfKVIW0EHA.3336@.TK2MSFTNGP11.phx.gbl...
> and
> to
> I
> permissions
> role
> and
> know
>|||Hello again,
Having looked through the help, I think what I want is a db_ddladmin. I
believe this will allow any users to add/modify tables....
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eJfPlgW0EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Unfortunately the application has been designed and written. It might be
> some work to change it all now! Surely there is an easy way to create a
> role or otherwise which will allow users to add or modify tables, but not
> give them full db_owner access?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ud5F0YW0EHA.3468@.TK2MSFTNGP14.phx.gbl...
>|||Hi
Look at db_datawriter,db_datareader fixed database role in the BOL.
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eJfPlgW0EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Unfortunately the application has been designed and written. It might be
> some work to change it all now! Surely there is an easy way to create a
> role or otherwise which will allow users to add or modify tables, but not
> give them full db_owner access?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ud5F0YW0EHA.3468@.TK2MSFTNGP14.phx.gbl...
procedures[vbcol=seagreen]
this?[vbcol=seagreen]
risky[vbcol=seagreen]
tables.[vbcol=seagreen]
them[vbcol=seagreen]
roles,[vbcol=seagreen]
create[vbcol=seagreen]
or[vbcol=seagreen]
>