Re: Permissions granting

From what you've said, it appears your requriements are:

1) Users can issue SELECT statements against any user created table or
view. How about INSERT, UPDATE and DELETE statements?

2) Users have EXECUTE permissions any user created stored procedure and
issue the appropriate commands against any user assembly that has been
loaded. I assume users cannot issue ALTER statements (e.g. ALTER
PROCEDURE) or DROP statements

3) Users should not be able to issue DDL commands (e.g. CREATE TABLE)
or modify the schema in any way. They can't create tables, views,
stored procs etc

Unfortunately, I don't think there is any fixed database role that has
the fine grain you are looking for. For example, the db_datareader role
will not give you all you want and the db_owner role will give you too
much.

I believe that the only way for you to get the kind of control you're
after is to manually define the permissions to what you want them to
be.

Sorry I can't be of more help.

.

Relevant Pages

  • Re: sql2k5 security
    ... you could give a user only Execute permissions on a ... stored procedure and the stored procedure could perform operations fine. ... Create a Schema named mySchema in the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Using xp_smtp_sendmail from a stored proc
    ... application that the user needs execute permissions on the extended stored ... > Grant EXECUTE permission on the stored procedure that you wrote ... >> grant users the execute right on the extended stored proc. ...
    (microsoft.public.sqlserver.security)
  • Re: Sql Server - Basic Permissions Question
    ... I have set EXECUTE permissions to the sp's as needed for the user. ... durring the execution of the stored procedure so long as the owner of the ... target table is the same as the owner of the stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure and Table permissions
    ... execute permissions on the stored procedure. ... >given the user EXEC permission for the stored procedure. ...
    (microsoft.public.sqlserver.security)