Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL User Allowed To Create/Maintain Views

Posted on 2011-03-10
8
Medium Priority
?
397 Views
Last Modified: 2012-05-11
I need to allow users to create views on our database using SQL Management studio, basically the user should be able to read data from all of the tables, execute any function and create/alter views.  They should not be able to alter/drop any tables/procedures/functions/etc.

What I have come up with so far is:

CREATE LOGIN MyLogin WITH PASSWORD='MyPa$$w0rd'
GO
CREATE USER MyUser FROM LOGIN MyLogin
GO
EXEC sp_addrolemember db_datareader, MyUser
EXEC sp_addrolemember db_denydatawriter, MyUser
GO
GRANT CREATE VIEW TO MyUser
GRANT EXECUTE TO MyUser
GRANT ALTER ON SCHEMA::[dbo] TO MyUser
GO

This works up to a point i.e.

- can read data
- cannot add/delete/update data
- can create views
- can execute functions

However this means that they can also alter/drop tables/functions/procedures.

My next thought was to use the INFORMATION_SCHEMA to loop though each table/function/procedure and manually deny ALTER and DROP but I cannot find a way to do this, if I try:

DENY DROP ON MyTable TO My User

It says "Incorrect syntax near 'DROP'."

Does the fact that I given the user rights to alter the schema, which is required in order to create a view, mean that I cannot stop them from dropping other objects in the database?

0
Comment
Question by:3Si_pnewman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 2

Expert Comment

by:LelikInside
ID: 35094421
DENY DROP Table TO MyUser
0
 
LVL 2

Expert Comment

by:LelikInside
ID: 35094432
or try

use Database
go
deny drop to user
0
 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35094588
I still get the same error using that syntax, it says:

Incorrect syntax near 'DROP'.

The actual SQL I am using is:

DENY DROP ON BELIEF TO OMNETQueryUser

Or

DENY DROP BELIEF TO OMNETQueryUser

both return the same error message.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35094616
Sorry I think I might have misunderstud what you ment.

I have now tried:

DENY DROP TABLE TO OMNETQueryUser
DENY DROP TO OMNETQueryUser

DENY DROP TABLE ON BELIEF TO OMNETQueryUser
DENY DROP TABLE BELIEF TO OMNETQueryUser

All return the same error.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35095057
>>Incorrect syntax near 'DROP'.<<
That would be because there is no "DENY DROP".
0
 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35095218

Thanks.  Going back to my original post then,

Does the fact that I given the user rights to alter the schema, which is required in order to create a view, mean that I cannot stop them from dropping other objects in the database?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35095238
That is my understanding.  Your best bet may be to add a DDL TRIGGER to prevent anyone changing the schema.
0
 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35095321

OK thanks.  I will give that a go.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question