Create View permission denied in ADP

[Penny]

I have set up an Access Data Project to access my sql server and don't
know how to restrict access to tables while allowing the end-user to
create new queries. In order to create a new "query" they can create a
view or stored procedure. Is it true that they can only do this if
they are the db_owner? If they have that role then they can do
anything to the database. How can I enable them to create a query but
have read-only access to the database and not be able to change any
tables or other queries/stored procedures?

I haven't seen any posts with others questioning this so I must be
missing something!

Thanks!

 

[Mary Chipman [MSFT]]

You're not missing anything -- the reason you haven't seen any posts
on the subject is because you can't do it. If you want this
functionality, then don't use an ADP. A better choice would be use a
linked-table mdb to connect to SQL Server. You could create views on
top of the tables you want people to write queries against, and grant
permissions on the views and not the underlying tables. In the mdb,
link to the views. Each person would be able to create and save their
own Access queries in their own local mdb and would not require any
elevated permissions on the SQL Server other than the permissions you
granted to the views. For a better understanding of how SQL Server
security works, see this "best practices" whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec02.mspx

--Mary

 

[Sylvain Lafontaine]

To be honest and for what I can remember, you're the first person who is
asking about the required permissions to create new views and stored
procedures and I absolutely don't know anything about this.

However, I'm not sure that giving permission to your users to create views
and stored procedures is really what you need. Why don't you explain why
you think that this is required in your case?

S. L.

 

[Penny]

I have just discovered that I can give permisssion to create a view
without giving full owner control. In Enterprise Mgr, right-click your
database and select Properties. Go to the Permissions tab, where you
can check "Create View" for the user or role you'd like to grant access
to. You can do the same thing by executing a "grant create view"
statement in Query Analyzer. I didn't realize that I can grant access
to statements as well as objects.

I had looked and looked before but didn't see this somewhat obvious
answer.

As to why I want to do this...this is a database that was migrated from
a legacy system with denormalized tables that are all read-only. I
want to use adp as a front-end query and reporting tool so the end-user
can search and report the data. There are probably lots of choices for
a front-end tool, but since most of the users already have Access, it
seemed like a good option.

Thanks for the replies...I'm all set now!

 

[Mary Chipman [MSFT]]

Use Access, just don't use an ADP. If you want users to store local
objects, then you can't do it--everything is on the server.

--Mary