Solved

SQL User Allowed To Create/Maintain Views

Posted on 2011-03-10
8
391 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

778 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