Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL User Allowed To Create/Maintain Views

Posted on 2011-03-10
8
Medium Priority
?
398 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

877 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