Re: Granting create table to database role

From: Uri Dimant (urid_at_iscar.co.il)
Date: 11/23/04 

Date: Tue, 23 Nov 2004 16:31:55 +0200

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...
> > 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...
> >> > 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...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: Minimum permissions for SQL2005 development?
    ... You need minimum level permission of db_ddladmin fixed database role so that ... For a developer, on the dev ... Anyone know what permissions I'd need to: ... effective server role is something like "Unauthenticated hacker". ...
    (microsoft.public.sqlserver.security)
  • Re: How to deny DBCC SQLPERF(logspace)
    ... permissions for each fixed database role. ... I would like to say that this query based on the two stored procedure ... server role or fixed database role. ...
    (microsoft.public.sqlserver.security)
  • Re: Alter Many Stored Procedures
    ... "Dan Guzman" wrote: ... > statement permissions like ALTER. ... > 2) a member of the db_ddladmin fixed database role ... > 5) a sysadmin server role member ...
    (microsoft.public.sqlserver.security)
  • Re: xp_startmail: wont run as non sa user
    ... Execute permissions for xp_startmail default to members of the db_owner ... fixed database role in the master database and members of the sysadmin fixed ... server role, but can be granted to other users. ...
    (microsoft.public.sqlserver.programming)
  • RE: Programatically find out stored procedures properties
    ... I did some searching in BOL since this is something that could be usefull ... This example lists the permissions for the titles table. ... This example lists all permissions that user Judy has in the current database. ...
    (microsoft.public.sqlserver.programming)