Read only permissions for report writer

I have an msde database (SQL Server 2000) with an web front end. I would
like to create an MS Access Project to allow certain users to create ad hoc
reports.
I have created a new login named Reporter and connected to the msde database
using this user. This user has dbreadonly permissions.
This works to a certain extent in that new reports can be created based on
existing tables. However, I would like the user to be able to create their
own select queries and cannot find a way to manage this. I recieve a
message saying the user needs 'Create Procedure' rights.
Can anyone tell me how I can let Reporter create their own queries but not
add/edit/delete any existing data?
Many thanks
June
hi June,
June Macleod wrote:
> I have an msde database (SQL Server 2000) with an web front end. I
> would like to create an MS Access Project to allow certain users to
> create ad hoc reports.
> I have created a new login named Reporter and connected to the msde
> database using this user. This user has dbreadonly permissions.
> This works to a certain extent in that new reports can be created
> based on existing tables. However, I would like the user to be able
> to create their own select queries and cannot find a way to manage
> this. I recieve a message saying the user needs 'Create Procedure'
> rights.
> Can anyone tell me how I can let Reporter create their own queries
> but not add/edit/delete any existing data?
> Many thanks
> June
if you are required to allow "CREATE PROC" statement you have to grant
membership to ddladmin database role but, with such a permission, Reporter
can even create new tables and of course access/modify their data..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||better,
if you only are required to create procedures, you can grant that specific
permission to Reporte user like
GRANT CREATE PROCEDURE TO [Reporter]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply